Introduction¶

This project represents our inaugural analysis of four companies within the commercial sector over a three-year period, from 2021 to 2023, segmented by quarters. The database utilized in this analysis is entirely hypothetical and does not reflect actual figures or pertain to any real-world company.

Through comprehensive data analysis, we have extracted key performance indicators (KPIs) for each of the four companies. Based on these KPIs, we have formulated conclusions and developed strategic recommendations for our organization.

Throughout this file, you will find five sections:

Section 1: Loading Financial Statement Data

Section 2: Loading Company Data

Section 3: Loading Sheets

Section 4: Graphs & Analysis

Section 5: Technical Solutions

Scenario: I am a financial analyst at Tech Company, which operates in a commercial market comprising four companies. Our company has been experiencing a decline in both product sales and profits over time, unlike our competitors who are enjoying high profitability.

->NOTE: If you have any confusing just read Print Function in the at the Code it will be highly enough.

Section 1: Loading Financial Statment Data¶

In [61]:
import pandas as pd

# Load the data from the Excel file
income_statement = pd.read_excel("D:/Project/Project_Four_Companies(Orginal).xlsx", sheet_name=0)
# Display the data
print(income_statement)
   Company Quarter  Year        Revenue           COGS   Gross_Profit  \
0     Tech      Q1  2021  648721.067585  365390.553223  283330.514362   
1     Tech      Q2  2021  565249.754651  260978.467697  304271.286954   
2     Tech      Q3  2021  758622.045788  454160.430384  304461.615404   
3     Tech      Q4  2021  669066.642633  350268.148408  318798.494225   
4     Tech      Q1  2022  881818.228323  429209.758804  452608.469519   
5     Tech      Q2  2022  518743.610482  278454.776529  240288.833953   
6     Tech      Q3  2022  656602.042863  344828.586156  311773.456707   
7     Tech      Q4  2022  922799.522699  489664.650759  433134.871940   
8     Tech      Q1  2023  639024.113874  348729.523457  290294.590418   
9     Tech      Q2  2023  595525.716668  336002.664362  259523.052306   
10    Tech      Q3  2023  869982.790405  390270.984178  479711.806227   
11    Tech      Q4  2023  731066.734889  436447.182319  294619.552570   
12   Speed      Q1  2021  657219.896957  333620.195106  323599.701850   
13   Speed      Q2  2021  980669.416730  576060.126769  404609.289961   
14   Speed      Q3  2021  551447.140951  314556.044680  236891.096271   
15   Speed      Q4  2021  787047.389353  347392.711681  439654.677671   
16   Speed      Q1  2022  641031.653250  308822.462947  332209.190303   
17   Speed      Q2  2022  563315.281537  310093.241999  253222.039539   
18   Speed      Q3  2022  994683.138278  502146.543435  492536.594843   
19   Speed      Q4  2022  658016.027782  344924.807930  313091.219852   
20   Speed      Q1  2023  574976.837636  330688.399495  244288.438140   
21   Speed      Q2  2023  677224.505265  282788.975064  394435.530201   
22   Speed      Q3  2023  900921.328154  386849.765961  514071.562193   
23   Speed      Q4  2023  520672.590515  230877.364243  289795.226272   
24   Maclr      Q1  2021  575229.744613  309856.269762  265373.474851   
25   Maclr      Q2  2021  588115.894438  331782.565483  256333.328955   
26   Maclr      Q3  2021  830948.139220  495346.279861  335601.859359   
27   Maclr      Q4  2021  610933.602098  327718.019841  283215.582257   
28   Maclr      Q1  2022  621120.156744  297236.377810  323883.778933   
29   Maclr      Q2  2022  844889.341463  459039.573737  385849.767726   
30   Maclr      Q3  2022  573363.850025  306861.701810  266502.148215   
31   Maclr      Q4  2022  849581.559369  352117.340746  497464.218622   
32   Maclr      Q1  2023  810912.543471  403149.840872  407762.702599   
33   Maclr      Q2  2023  634501.103981  256294.720735  378206.383246   
34   Maclr      Q3  2023  525939.659180  290613.016584  235326.642596   
35   Maclr      Q4  2023  723581.076825  348445.100355  375135.976470   
36    Dain      Q1  2021  582244.133098  316333.224678  265910.908420   
37    Dain      Q2  2021  600186.073299  334554.472986  265631.600313   
38    Dain      Q3  2021  950352.434181  556525.313618  393827.120563   
39    Dain      Q4  2021  970665.796932  471660.397862  499005.399070   
40    Dain      Q1  2022  512619.943003  214572.638369  298047.304635   
41    Dain      Q2  2022  562047.491459  268560.319206  293487.172253   
42    Dain      Q3  2022  690394.369552  402281.868634  288112.500918   
43    Dain      Q4  2022  892522.772136  534628.289309  357894.482827   
44    Dain      Q1  2023  786444.952238  346214.469206  440230.483032   
45    Dain      Q2  2023  916610.121470  455940.093058  460670.028412   
46    Dain      Q3  2023  744888.121062  362261.675096  382626.445965   
47    Dain      Q4  2023  883214.841891  404155.746803  479059.095088   

    Operating_Expenses           EBIT  Interest_Expense            EBT  \
0        189537.027856   93793.486506       1850.960091   91942.526415   
1        127754.920320  176516.366634       4469.459213  172046.907421   
2        172230.285950  132231.329454      11492.987498  120738.341955   
3        143989.634301  174808.859924       3852.762138  170956.097786   
4        256684.690605  195923.778914       5038.351632  190885.427282   
5        121972.105903  118316.728050       8258.503047  110058.225003   
6        133764.066144  178009.390563       9579.989569  168429.400995   
7        250773.384041  182361.487899       6491.117860  175870.370039   
8        178965.578110  111329.012308       4776.462794  106552.549514   
9        150405.733237  109117.319069       2377.097701  106740.221369   
10       212269.461629  267442.344599      12388.679382  255053.665217   
11       212876.604657   81742.947913       9481.011281   72261.936632   
12       186260.199537  137339.502314       7072.223069  130267.279245   
13       241333.436432  163275.853529       4385.402608  158890.450921   
14       163765.160809   73125.935462      12871.622100   60254.313361   
15       234290.131294  205364.546377       6518.184527  198846.361850   
16       171590.749994  160618.440309       3458.298139  157160.142171   
17       156533.630864   96688.408674       9234.772234   87453.636440   
18       286637.130819  205899.464024       7178.889225  198720.574799   
19       142804.850853  170286.368999      13523.581592  156762.787407   
20       132367.779794  111920.658346       2565.705570  109354.952776   
21       139115.226930  255320.303271       6512.006065  248808.297206   
22       216326.665072  297744.897122      13653.368680  284091.528442   
23       117836.743791  171958.482481       7281.633809  164676.848672   
24       155276.852228  110096.622622       9862.314161  100234.308461   
25       134266.296433  122067.032523       4383.931529  117683.100994   
26       231040.314646  104561.544713       5102.887925   99458.656789   
27       174450.322596  108765.259661       4116.618121  104648.641539   
28       173749.618160  150134.160773       6447.176095  143686.984678   
29       226940.111790  158909.655937      13039.513230  145870.142706   
30       153783.321439  112718.826776       6569.751437  106149.075339   
31       218593.603648  278870.614975       7505.947788  271364.667186   
32       194733.935850  213028.766749       7960.373835  205068.392914   
33       137244.278415  240962.104831       3815.635892  237146.468939   
34       148694.739420   86631.903177       5565.895869   81066.007308   
35       157471.077579  217664.898891      16793.808828  200871.090063   
36       174002.009698   91908.898722       9788.545935   82120.352786   
37       122685.047456  142946.552857       3878.051392  139068.501465   
38       272291.248229  121535.872334       4735.078825  116800.793509   
39       243955.990105  255049.408965      19282.765179  235766.643786   
40       145761.335974  152285.968661       2081.432359  150204.536302   
41       136948.845128  156538.327125       8831.127714  147707.199411   
42       147043.101111  141069.399807       9875.545553  131193.854254   
43       209652.387333  148242.095493       7789.504691  140452.590802   
44       219762.388431  220468.094600       6858.906781  213609.187820   
45       195880.849360  264789.179052       4908.732637  259880.446415   
46       166596.533304  216029.912662       4100.806406  211929.106256   
47       225677.598504  253381.496584      12799.077940  240582.418644   

     Tax_Expense     Net_Income  
0   27582.757925   64359.768491  
1   51614.072226  120432.835195  
2   36221.502587   84516.839369  
3   51286.829336  119669.268450  
4   57265.628185  133619.799097  
5   33017.467501   77040.757502  
6   50528.820298  117900.580696  
7   52761.111012  123109.259028  
8   31965.764854   74586.784660  
9   32022.066411   74718.154958  
10  76516.099565  178537.565652  
11  21678.580990   50583.355642  
12  39080.183774   91187.095472  
13  47667.135276  111223.315645  
14  18076.294008   42178.019353  
15  59653.908555  139192.453295  
16  47148.042651  110012.099519  
17  26236.090932   61217.545508  
18  59616.172440  139104.402359  
19  47028.836222  109733.951185  
20  32806.485833   76548.466943  
21  74642.489162  174165.808044  
22  85227.458532  198864.069909  
23  49403.054602  115273.794070  
24  30070.292538   70164.015923  
25  35304.930298   82378.170696  
26  29837.597037   69621.059752  
27  31394.592462   73254.049077  
28  43106.095403  100580.889275  
29  43761.042812  102109.099894  
30  31844.722602   74304.352737  
31  81409.400156  189955.267030  
32  61520.517874  143547.875040  
33  71143.940682  166002.528257  
34  24319.802192   56746.205116  
35  60261.327019  140609.763044  
36  24636.105836   57484.246950  
37  41720.550440   97347.951026  
38  35040.238053   81760.555456  
39  70729.993136  165036.650650  
40  45061.360891  105143.175412  
41  44312.159823  103395.039588  
42  39358.156276   91835.697978  
43  42135.777241   98316.813562  
44  64082.756346  149526.431474  
45  77964.133924  181916.312490  
46  63578.731877  148350.374379  
47  72174.725593  168407.693051  
In [62]:
import pandas as pd

# Load the data from the Excel file
balance_sheet_assets = pd.read_excel("D:/Project/Project_Four_Companies(Orginal).xlsx", sheet_name=1)
# Display the data
print(balance_sheet_assets)
   Company Quarter  Year           Cash  Accounts_Receivable      Inventory  \
0     Tech      Q1  2021  108279.696035         67301.480271   60332.733781   
1     Tech      Q2  2021   74652.767419         57080.081988   36564.923231   
2     Tech      Q3  2021   59313.196864         84926.294392   59167.691606   
3     Tech      Q4  2021  121789.993433        108083.501404   66410.622551   
4     Tech      Q1  2022   55973.299397        113649.646460   48630.345990   
5     Tech      Q2  2022   80702.406964        102817.464981   33237.503491   
6     Tech      Q3  2022   88387.917836        127710.333988   61840.110524   
7     Tech      Q4  2022   67933.917113        129024.817002   86907.425269   
8     Tech      Q1  2023   72952.388632         67318.845809   69029.141373   
9     Tech      Q2  2023  132510.502436         92360.447932   46536.503733   
10    Tech      Q3  2023  114973.734610        163541.072629   42600.437868   
11    Tech      Q4  2023   54694.928870         81078.834324   84750.925919   
12   Speed      Q1  2021  114200.958565        124978.442384   56890.770934   
13   Speed      Q2  2021   55393.163298        170536.942104  101007.666361   
14   Speed      Q3  2021  117534.229189         80479.464745   47351.054548   
15   Speed      Q4  2021   63231.309142        114318.401144   53935.839404   
16   Speed      Q1  2022   90856.098361         64648.434991   51327.357278   
17   Speed      Q2  2022  127823.384778         80929.313367   50100.415963   
18   Speed      Q3  2022   92628.795257        128246.640960   65873.228690   
19   Speed      Q4  2022   93627.622513        123767.419893   41367.329930   
20   Speed      Q1  2023  109553.637225         91860.145597   52642.712428   
21   Speed      Q2  2023   56287.806963        106295.400781   38849.674840   
22   Speed      Q3  2023   55335.636796        108446.813290   51039.874238   
23   Speed      Q4  2023  110498.621555         77885.958645   25331.661291   
24   Maclr      Q1  2021  114329.622685         61252.409840   44240.200578   
25   Maclr      Q2  2021   52047.859224        108248.985419   66051.513408   
26   Maclr      Q3  2021   82547.406816        112198.914000   95320.513804   
27   Maclr      Q4  2021   88719.577469         97199.953035   49054.201332   
28   Maclr      Q1  2022   60878.424166        114293.748138   34611.873560   
29   Maclr      Q2  2022   76410.680119        129879.638859   50995.793117   
30   Maclr      Q3  2022  121765.790647         71789.151836   60030.982164   
31   Maclr      Q4  2022   57241.350272        143619.666143   48755.255683   
32   Maclr      Q1  2023  144067.086030         86458.386243   58828.282221   
33   Maclr      Q2  2023   66584.988635         66362.609494   49833.435655   
34   Maclr      Q3  2023   58024.924361         89492.608388   32418.398692   
35   Maclr      Q4  2023   94108.740815        120419.186887   41572.361866   
36    Dain      Q1  2021   73285.315978         79141.419454   62188.751579   
37    Dain      Q2  2021  118273.859230        104071.220494   53932.817013   
38    Dain      Q3  2021   81358.059785        141022.999036  101902.262050   
39    Dain      Q4  2021   68008.399448        129743.670693   84692.004497   
40    Dain      Q1  2022  109980.258299         71891.931701   33210.371856   
41    Dain      Q2  2022   86082.644179         91392.582367   37634.770072   
42    Dain      Q3  2022  128536.022781         87550.193196   66957.274746   
43    Dain      Q4  2022  104446.657568        105627.711046   73960.040679   
44    Dain      Q1  2023   77456.698923        143408.969724   38353.641467   
45    Dain      Q2  2023   55170.427293        139964.760849   46134.388450   
46    Dain      Q3  2023   65166.805748        127152.102372   42306.544151   
47    Dain      Q4  2023  136866.907861        153258.047556   62151.026956   

    Current_Assets  Property_Plant_Equipment  Total_Assets  
0    235913.910088             443445.515864  6.793594e+05  
1    168297.772638             511039.291010  6.793371e+05  
2    203407.182862             722988.496856  9.263957e+05  
3    296284.117388             429426.488099  7.257106e+05  
4    218253.291847             678703.482365  8.969568e+05  
5    216757.375436             316050.540578  5.328079e+05  
6    277938.362348             612841.345175  8.907797e+05  
7    283866.159384             641801.660560  9.256678e+05  
8    209300.375814             552724.351005  7.620247e+05  
9    271407.454102             451041.792897  7.224492e+05  
10   321115.245106             821941.023532  1.143056e+06  
11   220524.689113             428255.990883  6.487807e+05  
12   296070.171883             633617.792364  9.296880e+05  
13   326937.771762             797465.022287  1.124403e+06  
14   245364.748481             350245.821518  5.956106e+05  
15   231485.549690             742286.382828  9.737719e+05  
16   206831.890630             472692.582276  6.795245e+05  
17   258853.114108             539829.064392  7.986822e+05  
18   286748.664908             601559.502124  8.883082e+05  
19   258762.372336             572648.067953  8.314104e+05  
20   254056.495250             539313.047440  7.933695e+05  
21   201432.882584             482278.109886  6.837110e+05  
22   214822.324324             793786.863780  1.008609e+06  
23   213716.241491             299182.516403  5.128988e+05  
24   219822.233103             368781.788110  5.886040e+05  
25   226348.358051             301008.887267  5.273572e+05  
26   290066.834620             438239.899446  7.283067e+05  
27   234973.731835             461854.272302  6.968280e+05  
28   209784.045864             329171.068696  5.389551e+05  
29   257286.112095             795165.525293  1.052452e+06  
30   253585.924647             553413.775055  8.069997e+05  
31   249616.272097             590738.570516  8.403548e+05  
32   289353.754493             419547.772412  7.089015e+05  
33   182781.033784             402763.089912  5.855441e+05  
34   179935.931441             274814.115212  4.547500e+05  
35   256100.289569             700231.396819  9.563317e+05  
36   214615.487011             302315.846607  5.169313e+05  
37   276277.896736             341741.063076  6.180190e+05  
38   324283.320870             584137.893142  9.084212e+05  
39   282444.074638             695753.771147  9.781978e+05  
40   215082.561856             411458.155378  6.265407e+05  
41   215109.996617             547018.771991  7.621288e+05  
42   283043.490723             482858.694956  7.659022e+05  
43   284034.409294             712915.981888  9.969504e+05  
44   259219.310114             615757.357389  8.749767e+05  
45   241269.576591             681385.054424  9.226546e+05  
46   234625.452270             581577.500304  8.162030e+05  
47   352275.982373             523842.826028  8.761188e+05  
In [63]:
import pandas as pd

# Load the data from the Excel file
balance_sheet_liabilities_shareholders = pd.read_excel("D:/Project/Project_Four_Companies(Orginal).xlsx", sheet_name=2)
# Display the data
print(balance_sheet_liabilities_shareholders)
   Company Quarter  Year  Accounts_Payable  Short_Term_Debt  \
0     Tech      Q1  2021      64309.800422     82956.441273   
1     Tech      Q2  2021      34109.296172     64509.680525   
2     Tech      Q3  2021      52608.751658    114660.138863   
3     Tech      Q4  2021      47387.206854    107439.319696   
4     Tech      Q1  2022      47422.297362    130525.295078   
5     Tech      Q2  2022      51468.743384     70189.093059   
6     Tech      Q3  2022      68961.783438     93159.725128   
7     Tech      Q4  2022      92628.227070     98568.265844   
8     Tech      Q1  2023      56606.277594     90380.428100   
9     Tech      Q2  2023      43996.898421     82808.495316   
10    Tech      Q3  2023      62431.051884    128085.232662   
11    Tech      Q4  2023      64435.161984    125725.503383   
12   Speed      Q1  2021      64771.920422     74728.733236   
13   Speed      Q2  2021      78769.910648    192557.790972   
14   Speed      Q3  2021      46571.558397     84371.092592   
15   Speed      Q4  2021      62834.391630     92974.182260   
16   Speed      Q1  2022      38498.011463    119103.516480   
17   Speed      Q2  2022      43218.872800     98777.732044   
18   Speed      Q3  2022      94898.327698    192935.934117   
19   Speed      Q4  2022      49201.480085     85095.608000   
20   Speed      Q1  2023      58342.879803     77500.826460   
21   Speed      Q2  2023      44772.335254    100302.472458   
22   Speed      Q3  2023      62280.781267    121022.819985   
23   Speed      Q4  2023      28680.217522     97510.131777   
24   Maclr      Q1  2021      34330.440782     58318.147552   
25   Maclr      Q2  2021      42399.729607     97853.552243   
26   Maclr      Q3  2021      65109.639988    107651.438546   
27   Maclr      Q4  2021      58548.830392    121313.390078   
28   Maclr      Q1  2022      50650.587487    119399.849551   
29   Maclr      Q2  2022      85334.919750     88028.441808   
30   Maclr      Q3  2022      40484.687111     65364.472857   
31   Maclr      Q4  2022      69875.200159     87217.979253   
32   Maclr      Q1  2023      59586.469154    109524.717745   
33   Maclr      Q2  2023      41489.507416    122398.895253   
34   Maclr      Q3  2023      47916.562870     98455.202868   
35   Maclr      Q4  2023      63729.296226    134468.836469   
36    Dain      Q1  2021      57001.904841    113184.376700   
37    Dain      Q2  2021      39148.160979     65784.443944   
38    Dain      Q3  2021      85215.653741    113397.108303   
39    Dain      Q4  2021      50291.843849    106790.511322   
40    Dain      Q1  2022      28161.194211    102248.954843   
41    Dain      Q2  2022      27862.664838     96622.817872   
42    Dain      Q3  2022      43685.585153     82916.839636   
43    Dain      Q4  2022      91711.394045    173418.599302   
44    Dain      Q1  2023      64621.399081     85810.192856   
45    Dain      Q2  2023      76721.488067    160292.561678   
46    Dain      Q3  2023      44534.116390    144498.708322   
47    Dain      Q4  2023      44532.018626    149465.827102   

    Current_Liabilities  Long_Term_Debt  Total_Liabilities   Common_Stock  \
0         147266.241696   160837.405639      308103.647335   71442.260673   
1          98618.976697   127203.576661      225822.553358   79473.945412   
2         167268.890522   162519.726056      329788.616577  171887.461570   
3         154826.526550   201877.945152      356704.471702  106769.211805   
4         177947.592440   209180.185694      387127.778134  177363.248074   
5         121657.836444   202106.729145      323764.565588   79501.335949   
6         162121.508566   202315.632138      364437.140704   90511.274235   
7         191196.492915   284823.054703      476019.547617  128472.552038   
8         146986.705694   251021.535338      398008.241033  125989.793707   
9         126805.393737   144322.377579      271127.771315  128804.339040   
10        190516.284546   244333.512208      434849.796754  157226.023928   
11        190160.665367   258044.269711      448204.935078   75005.398035   
12        139500.653658   162815.305069      302315.958726  101204.600286   
13        271327.701619   261116.691065      532444.392684  135087.183092   
14        130942.650990   112767.601076      243710.252065   73998.368590   
15        155808.573890   240255.116354      396063.690244  147404.434999   
16        157601.527943   170774.448605      328375.976548   86297.836690   
17        141996.604845   196709.249642      338705.854487  147594.093470   
18        287834.261815   267546.792364      555381.054179  141487.627599   
19        134297.088085   221145.435747      355442.523832  150633.427866   
20        135843.706263   213838.672894      349682.379157  156983.310467   
21        145074.807712   246812.247460      391887.055172   72032.584097   
22        183303.601252   192408.619406      375712.220658  156985.468856   
23        126190.349299   143953.196793      270143.546092   99425.099719   
24         92648.588334   197550.469051      290199.057385   66111.070828   
25        140253.281850   197770.515940      338023.797790   71067.775484   
26        172761.078534   298476.759049      471237.837583   82858.096016   
27        179862.220470   123229.520445      303091.740915   97538.368103   
28        170050.437038   189843.723084      359894.160123   55472.614933   
29        173363.361558   200333.243980      373696.605538  196869.516188   
30        105849.159968   188357.382715      294206.542684   86380.186846   
31        157093.179412   278273.101680      435366.281093  140732.952801   
32        169111.186899   318536.362368      487647.549267  114434.439992   
33        163888.402669   135318.123573      299206.526242  111399.702471   
34        146371.765739   197093.591458      343465.357196   78447.021999   
35        198198.132695   232472.316075      430670.448770   98406.623277   
36        170186.281541   142841.272759      313027.554300   82809.532906   
37        104932.604923   183691.787790      288624.392713  119990.349423   
38        198612.762044   303768.045094      502380.807138  152858.497983   
39        157082.355170   253633.385771      410715.740941  115136.756757   
40        130410.149053   116986.571635      247396.720689   88604.123459   
41        124485.482710   172560.823054      297046.305764  128558.153374   
42        126602.424789   192777.139659      319379.564449   93566.527848   
43        265129.993347   260814.037406      525944.030753  179643.551441   
44        150431.591936   168638.687559      319070.279496  149287.534696   
45        237014.049746   326489.899508      563503.949253  111183.353949   
46        189032.824712   177712.227403      366745.052115   89137.960444   
47        193997.845728   286846.550645      480844.396373  104564.670474   

    Retained_Earnings  Shareholders_Equity  Total_Liabilities_and_Equity  
0       299813.517944        371255.778617                  6.793594e+05  
1       374040.564878        453514.510290                  6.793371e+05  
2       424719.601570        596607.063141                  9.263957e+05  
3       262236.921979        369006.133785                  7.257106e+05  
4       332465.748004        509828.996078                  8.969568e+05  
5       129542.014477        209043.350426                  5.328079e+05  
6       435831.292584        526342.566819                  8.907797e+05  
7       321175.720288        449648.272327                  9.256678e+05  
8       238026.692080        364016.485786                  7.620247e+05  
9       322517.136643        451321.475683                  7.224492e+05  
10      550980.447956        708206.471884                  1.143056e+06  
11      125570.346883        200575.744918                  6.487807e+05  
12      526167.405235        627372.005521                  9.296880e+05  
13      456871.218274        591958.401366                  1.124403e+06  
14      277901.949344        351900.317934                  5.956106e+05  
15      430303.807275        577708.242274                  9.737719e+05  
16      264850.659669        351148.496358                  6.795245e+05  
17      312382.230543        459976.324013                  7.986822e+05  
18      191439.485254        332927.112853                  8.883082e+05  
19      325334.488591        475967.916457                  8.314104e+05  
20      286703.853066        443687.163533                  7.933695e+05  
21      219791.353200        291823.937298                  6.837110e+05  
22      475911.498590        632896.967447                  1.008609e+06  
23      143330.112082        242755.211801                  5.128988e+05  
24      232293.893000        298404.963828                  5.886040e+05  
25      118265.672044        189333.447528                  5.273572e+05  
26      174210.800467        257068.896483                  7.283067e+05  
27      296197.895120        393736.263223                  6.968280e+05  
28      123588.339504        179060.954437                  5.389551e+05  
29      481885.515662        678755.031850                  1.052452e+06  
30      426412.970172        512793.157018                  8.069997e+05  
31      264255.608720        404988.561521                  8.403548e+05  
32      106819.537646        221253.977638                  7.089015e+05  
33      174937.894983        286337.597454                  5.855441e+05  
34       32837.667458        111284.689457                  4.547500e+05  
35      427254.614340        525661.237618                  9.563317e+05  
36      121094.246412        203903.779318                  5.169313e+05  
37      209404.217677        329394.567100                  6.180190e+05  
38      253181.908891        406040.406874                  9.084212e+05  
39      452345.348087        567482.104844                  9.781978e+05  
40      290539.873086        379143.996545                  6.265407e+05  
41      336524.309470        465082.462844                  7.621288e+05  
42      352956.093382        446522.621230                  7.659022e+05  
43      291362.808987        471006.360429                  9.969504e+05  
44      406618.853311        555906.388007                  8.749767e+05  
45      247967.327813        359150.681762                  9.226546e+05  
46      360319.940016        449457.900460                  8.162030e+05  
47      290709.741554        395274.412028                  8.761188e+05  

Section 2: Loading Companies Data¶

In [64]:
import pandas as pd

# Load the data from the Excel file
tech = pd.read_excel("D:/Project/P_Adjusted2.xlsx", sheet_name=0)

# Display the data
print(tech)
                         Income Statement Unnamed: 1 Unnamed: 2 Unnamed: 3  \
0                                     NaN       2021        NaN        NaN   
1                                 Quarter         Q1         Q2         Q3   
2                                 Revenue     648721     565250     758622   
3                                    COGS     365391     260978     454160   
4                            Gross_Profit     283331     304271     304462   
5                      Operating_Expenses     189537     127755     172230   
6                                    EBIT      93793     176516     132231   
7                        Interest_Expense       1851       4469      11493   
8                                     EBT      91943     172047     120738   
9                             Tax_Expense      27583      51614      36222   
10                             Net_Income      64360     120433      84517   
11                          Balance Sheet        NaN        NaN        NaN   
12                                 Assets        NaN        NaN        NaN   
13                                    NaN       2021        NaN        NaN   
14                                Quarter         Q1         Q2         Q3   
15                                   Cash     108280      74653      59313   
16                    Accounts_Receivable      67301      57080      84926   
17                              Inventory      60333      36565      59168   
18                         Current_Assets     235914     168298     203407   
19               Property_Plant_Equipment     443446     511039     722988   
20                           Total_Assets     679359     679337     926396   
21       Liability & Shareholder's Equity        NaN        NaN        NaN   
22                                    NaN       2021        NaN        NaN   
23                                Quarter         Q1         Q2         Q3   
24                       Accounts_Payable      64310      34109      52609   
25                        Short_Term_Debt      82956      64510     114660   
26                    Current_Liabilities     147266      98619     167269   
27                         Long_Term_Debt     160837     127204     162520   
28                      Total_Liabilities     308104     225823     329789   
29                           Common_Stock      71442      79474     171887   
30                      Retained_Earnings     299814     374041     424720   
31                    Shareholders_Equity     371256     453515     596607   
32           Total_Liabilities_and_Equity     679359     679337     926396   

   Unnamed: 4 Unnamed: 5 Unnamed: 6 Unnamed: 7 Unnamed: 8 Unnamed: 9  \
0         NaN       2022        NaN        NaN        NaN       2023   
1          Q4         Q1         Q2         Q3         Q4         Q1   
2      669067     881818     518744     656602     922800     639024   
3      350268     429210     278455     344829     489665     348730   
4      318798     452608     240289     311773     433135     290295   
5      143990     256685     121972     133764     250773     178966   
6      174809     195924     118317     178009     182361     111329   
7        3853       5038       8259       9580       6491       4776   
8      170956     190885     110058     168429     175870     106553   
9       51287      57266      33017      50529      52761      31966   
10     119669     133620      77041     117901     123109      74587   
11        NaN        NaN        NaN        NaN        NaN        NaN   
12        NaN        NaN        NaN        NaN        NaN        NaN   
13        NaN       2022        NaN        NaN        NaN       2023   
14         Q4         Q1         Q2         Q3         Q4         Q1   
15     121790      55973      80702      88388      67934      72952   
16     108084     113650     102817     127710     129025      67319   
17      66411      48630      33238      61840      86907      69029   
18     296284     218253     216757     277938     283866     209300   
19     429426     678703     316051     612841     641802     552724   
20     725711     896957     532808     890780     925668     762025   
21        NaN        NaN        NaN        NaN        NaN        NaN   
22        NaN       2022        NaN        NaN        NaN       2023   
23         Q4         Q1         Q2         Q3         Q4         Q1   
24      47387      47422      51469      68962      92628      56606   
25     107439     130525      70189      93160      98568      90380   
26     154827     177948     121658     162122     191196     146987   
27     201878     209180     202107     202316     284823     251022   
28     356704     387128     323765     364437     476020     398008   
29     106769     177363      79501      90511     128473     125990   
30     262237     332466     129542     435831     321176     238027   
31     369006     509829     209043     526343     449648     364016   
32     725711     896957     532808     890780     925668     762025   

   Unnamed: 10 Unnamed: 11 Unnamed: 12  
0          NaN         NaN         NaN  
1           Q2          Q3          Q4  
2       595526      869983      731067  
3       336003      390271      436447  
4       259523      479712      294620  
5       150406      212269      212877  
6       109117      267442       81743  
7         2377       12389        9481  
8       106740      255054       72262  
9        32022       76516       21679  
10       74718      178538       50583  
11         NaN         NaN         NaN  
12         NaN         NaN         NaN  
13         NaN         NaN         NaN  
14          Q2          Q3          Q4  
15      132511      114974       54695  
16       92360      163541       81079  
17       46537       42600       84751  
18      271407      321115      220525  
19      451042      821941      428256  
20      722449     1143056      648781  
21         NaN         NaN         NaN  
22         NaN         NaN         NaN  
23          Q2          Q3          Q4  
24       43997       62431       64435  
25       82808      128085      125726  
26      126805      190516      190161  
27      144322      244334      258044  
28      271128      434850      448205  
29      128804      157226       75005  
30      322517      550980      125570  
31      451321      708206      200576  
32      722449     1143056      648781  
In [65]:
import pandas as pd

# Load the data from the Excel file
speed = pd.read_excel("D:/Project/P_Adjusted2.xlsx", sheet_name=1)

# Display the data
print(speed)
                         Income Statement Unnamed: 1 Unnamed: 2 Unnamed: 3  \
0                                     NaN       2021        NaN        NaN   
1                                 Quarter         Q1         Q2         Q3   
2                                 Revenue     657220     980669     551447   
3                                    COGS     333620     576060     314556   
4                            Gross_Profit     283331     404609     236891   
5                      Operating_Expenses     186260     127755     163765   
6                                    EBIT      93793     276854      73126   
7                        Interest_Expense       7072       4469      12872   
8                                     EBT     130267     158890      60254   
9                             Tax_Expense      39080      47667      18076   
10                             Net_Income      91187     111223      42178   
11                          Balance Sheet        NaN        NaN        NaN   
12                                 Assets        NaN        NaN        NaN   
13                                    NaN       2021        NaN        NaN   
14                                Quarter         Q1         Q2         Q3   
15                                   Cash     114201      55393     117534   
16                    Accounts_Receivable     124978     170537      80479   
17                              Inventory      56891     101008      47351   
18                         Current_Assets     296070     326938     245365   
19               Property_Plant_Equipment     633618     797465     350246   
20                           Total_Assets     929688    1124403     595611   
21       Liability & Shareholder's Equity        NaN        NaN        NaN   
22                                    NaN       2021        NaN        NaN   
23                                Quarter         Q1         Q2         Q3   
24                       Accounts_Payable      64772      78770      46572   
25                        Short_Term_Debt      74729     192558      84371   
26                    Current_Liabilities     139501     271328     130943   
27                         Long_Term_Debt     162815     261117     112768   
28                      Total_Liabilities     302316     532444     243710   
29                           Common_Stock     101205     135087      73998   
30                      Retained_Earnings     526167     456871     277902   
31                    Shareholders_Equity     627372     591958     351900   
32           Total_Liabilities_and_Equity     929688    1124403     595611   

   Unnamed: 4 Unnamed: 5 Unnamed: 6 Unnamed: 7 Unnamed: 8 Unnamed: 9  \
0         NaN       2022        NaN        NaN        NaN       2023   
1          Q4         Q1         Q2         Q3         Q4         Q1   
2      787047     641032     563315     994683     658016     574977   
3      347393     308822     310093     502147     344925     330688   
4      439655     332209     253222     492537     313091     244288   
5      234290     171591     156534     286637     142805     132368   
6      205365     160618      96688     205899     170286     111921   
7        6518       3458       9235       7179      13524       2566   
8      198846     157160      87454     198721     156763     109355   
9       59654      47148      26236      59616      47029      32806   
10     139192     110012      61218     139104     109734      76548   
11        NaN        NaN        NaN        NaN        NaN        NaN   
12        NaN        NaN        NaN        NaN        NaN        NaN   
13        NaN       2022        NaN        NaN        NaN       2023   
14         Q4         Q1         Q2         Q3         Q4         Q1   
15      63231      90856     127823      92629      93628     109554   
16     114318      64648      80929     128247     123767      91860   
17      53936      51327      50100      65873      41367      52643   
18     231486     206832     258853     286749     258762     254056   
19     742286     472693     539829     601560     572648     539313   
20     973772     679524     798682     888308     831410     793370   
21        NaN        NaN        NaN        NaN        NaN        NaN   
22        NaN       2022        NaN        NaN        NaN       2023   
23         Q4         Q1         Q2         Q3         Q4         Q1   
24      62834      38498      43219      94898      49201      58343   
25      92974     119104      98778     192936      85096      77501   
26     155809     157602     141997     287834     134297     135844   
27     240255     170774     196709     267547     221145     213839   
28     396064     328376     338706     555381     355443     349682   
29     147404      86298     147594     141488     150633     156983   
30     430304     264851     312382     191439     325334     286704   
31     577708     351148     459976     332927     475968     443687   
32     973772     679524     798682     888308     831410     793370   

   Unnamed: 10 Unnamed: 11 Unnamed: 12  
0          NaN         NaN         NaN  
1           Q2          Q3          Q4  
2       677225      900921      520673  
3       282789      386850      230877  
4       394436      514072      289795  
5       139115      216327      117837  
6       255320      297745      171958  
7         6512       13653        7282  
8       248808      284092      164677  
9        74642       85227       49403  
10      174166      198864      115274  
11         NaN         NaN         NaN  
12         NaN         NaN         NaN  
13         NaN         NaN         NaN  
14          Q2          Q3          Q4  
15       56288       55336      110499  
16      106295      108447       77886  
17       38850       51040       25332  
18      201433      214822      213716  
19      482278      793787      299183  
20      683711     1008609      512899  
21         NaN         NaN         NaN  
22         NaN         NaN         NaN  
23          Q2          Q3          Q4  
24       44772       62281       28680  
25      100302      121023       97510  
26      145075      183304      126190  
27      246812      192409      143953  
28      391887      375712      270144  
29       72033      156985       99425  
30      219791      475911      143330  
31      291824      632897      242755  
32      683711     1008609      512899  
In [66]:
import pandas as pd

# Load the data from the Excel file
maclr = pd.read_excel("D:/Project/P_Adjusted2.xlsx", sheet_name=2)

# Display the data
print(maclr)
                         Income Statement Unnamed: 1 Unnamed: 2 Unnamed: 3  \
0                                     NaN       2021        NaN        NaN   
1                                 Quarter         Q1         Q2         Q3   
2                                 Revenue     575230     588116     830948   
3                                    COGS     309856     331783     495346   
4                            Gross_Profit     283331     256333     335602   
5                      Operating_Expenses     155277     127755     231040   
6                                    EBIT      93793     128578     104562   
7                        Interest_Expense       9862       4469       5103   
8                                     EBT     100234     117683      99459   
9                             Tax_Expense      30070      35305      29838   
10                             Net_Income      70164      82378      69621   
11                          Balance Sheet        NaN        NaN        NaN   
12                                 Assets        NaN        NaN        NaN   
13                                    NaN       2021        NaN        NaN   
14                                Quarter         Q1         Q2         Q3   
15                                   Cash     114330      52048      82547   
16                    Accounts_Receivable      61252     108249     112199   
17                              Inventory      44240      66052      95321   
18                         Current_Assets     219822     226348     290067   
19               Property_Plant_Equipment     368782     301009     438240   
20                           Total_Assets     588604     527357     728307   
21       Liability & Shareholder's Equity        NaN        NaN        NaN   
22                                    NaN       2021        NaN        NaN   
23                                Quarter         Q1         Q2         Q3   
24                       Accounts_Payable      34330      42400      65110   
25                        Short_Term_Debt      58318      97854     107651   
26                    Current_Liabilities      92649     140253     172761   
27                         Long_Term_Debt     197550     197771     298477   
28                      Total_Liabilities     290199     338024     471238   
29                           Common_Stock      66111      71068      82858   
30                      Retained_Earnings     232294     118266     174211   
31                    Shareholders_Equity     298405     189333     257069   
32           Total_Liabilities_and_Equity     588604     527357     728307   

   Unnamed: 4 Unnamed: 5 Unnamed: 6 Unnamed: 7 Unnamed: 8 Unnamed: 9  \
0         NaN       2022        NaN        NaN        NaN       2023   
1          Q4         Q1         Q2         Q3         Q4         Q1   
2      610934     621120     844889     573364     849582     810913   
3      327718     297236     459040     306862     352117     403150   
4      283216     323884     385850     266502     497464     407763   
5      174450     173750     226940     153783     218594     194734   
6      108765     150134     158910     112719     278871     213029   
7        4117       6447      13040       6570       7506       7960   
8      104649     143687     145870     106149     271365     205068   
9       31395      43106      43761      31845      81409      61521   
10      73254     100581     102109      74304     189955     143548   
11        NaN        NaN        NaN        NaN        NaN        NaN   
12        NaN        NaN        NaN        NaN        NaN        NaN   
13        NaN       2022        NaN        NaN        NaN       2023   
14         Q4         Q1         Q2         Q3         Q4         Q1   
15      88720     114330      52048      82547      88720      60878   
16      97200      61252     108249     112199      97200     114294   
17      49054      44240      66052      95321      49054      34612   
18     234974     219822     226348     290067     234974     209784   
19     461854     368782     301009     438240     461854     329171   
20     696828     588604     527357     728307     696828     538955   
21        NaN        NaN        NaN        NaN        NaN        NaN   
22        NaN       2022        NaN        NaN        NaN       2023   
23         Q4         Q1         Q2         Q3         Q4         Q1   
24      58549      34330      42400      65110      58549      50651   
25     121313      58318      97854     107651     121313     119400   
26     179862      92649     140253     172761     179862     170050   
27     123230     197550     197771     298477     123230     189844   
28     303092     290199     338024     471238     303092     359894   
29      97538      66111      71068      82858      97538      55473   
30     296198     232294     118266     174211     296198     123588   
31     393736     298405     189333     257069     393736     179061   
32     696828     588604     527357     728307     696828     538955   

   Unnamed: 10 Unnamed: 11 Unnamed: 12  
0          NaN         NaN         NaN  
1           Q2          Q3          Q4  
2       634501      525940      723581  
3       256295      290613      348445  
4       378206      235327      375136  
5       137244      148695      157471  
6       240962       86632      217665  
7         3816        5566       16794  
8       237146       81066      200871  
9        71144       24320       60261  
10      166003       56746      140610  
11         NaN         NaN         NaN  
12         NaN         NaN         NaN  
13         NaN         NaN         NaN  
14          Q2          Q3          Q4  
15       76411      121766       57241  
16      129880       71789      143620  
17       50996       60031       48755  
18      257286      253586      249616  
19      795166      553414      590739  
20     1052452      807000      840355  
21         NaN         NaN         NaN  
22         NaN         NaN         NaN  
23          Q2          Q3          Q4  
24       85335       40485       69875  
25       88028       65364       87218  
26      173363      105849      157093  
27      200333      188357      278273  
28      373697      294207      435366  
29      196870       86380      140733  
30      481886      426413      264256  
31      678755      512793      404989  
32     1052452      807000      840355  
In [67]:
import pandas as pd

# Load the data from the Excel file
dain = pd.read_excel("D:/Project/P_Adjusted2.xlsx", sheet_name=3)

# Display the data
print(dain)
                         Income Statement Unnamed: 1 Unnamed: 2 Unnamed: 3  \
0                                     NaN       2021        NaN        NaN   
1                                 Quarter         Q1         Q2         Q3   
2                                 Revenue     582244     600186     950352   
3                                    COGS     316333     334554     556525   
4                            Gross_Profit     283331     265632     393827   
5                      Operating_Expenses     174002     127755     272291   
6                                    EBIT      93793     137877     121536   
7                        Interest_Expense       9789       4469       4735   
8                                     EBT      82120     139069     116801   
9                             Tax_Expense      24636      41721      35040   
10                             Net_Income      57484      97348      81761   
11                          Balance Sheet        NaN        NaN        NaN   
12                                 Assets        NaN        NaN        NaN   
13                                    NaN       2021        NaN        NaN   
14                                Quarter         Q1         Q2         Q3   
15                                   Cash      73285     118274      81358   
16                    Accounts_Receivable      79141     104071     141023   
17                              Inventory      62189      53933     101902   
18                         Current_Assets     214615     276278     324283   
19               Property_Plant_Equipment     302316     341741     584138   
20                           Total_Assets     516931     618019     908421   
21       Liability & Shareholder's Equity        NaN        NaN        NaN   
22                                    NaN       2021        NaN        NaN   
23                                Quarter         Q1         Q2         Q3   
24                       Accounts_Payable      57002      39148      85216   
25                        Short_Term_Debt     113184      65784     113397   
26                    Current_Liabilities     170186     104933     198613   
27                         Long_Term_Debt     142841     183692     303768   
28                      Total_Liabilities     313028     288624     502381   
29                           Common_Stock      82810     119990     152858   
30                      Retained_Earnings     121094     209404     253182   
31                    Shareholders_Equity     203904     329395     406040   
32           Total_Liabilities_and_Equity     516931     618019     908421   

   Unnamed: 4 Unnamed: 5 Unnamed: 6 Unnamed: 7 Unnamed: 8 Unnamed: 9  \
0         NaN       2022        NaN        NaN        NaN       2023   
1          Q4         Q1         Q2         Q3         Q4         Q1   
2      970666     512620     562047     690394     892523     786445   
3      471660     214573     268560     402282     534628     346214   
4      499005     298047     293487     288113     357894     440230   
5      243956     145761     136949     147043     209652     219762   
6      255049     152286     156538     141069     148242     220468   
7       19283       2081       8831       9876       7790       6859   
8      235767     150205     147707     131194     140453     213609   
9       70730      45061      44312      39358      42136      64083   
10     165037     105143     103395      91836      98317     149526   
11        NaN        NaN        NaN        NaN        NaN        NaN   
12        NaN        NaN        NaN        NaN        NaN        NaN   
13        NaN       2022        NaN        NaN        NaN       2023   
14         Q4         Q1         Q2         Q3         Q4         Q1   
15      68008     109980      86083     128536     104447      77457   
16     129744      71892      91393      87550     105628     143409   
17      84692      33210      37635      66957      73960      38354   
18     282444     215083     215110     283043     284034     259219   
19     695754     411458     547019     482859     712916     615757   
20     978198     626541     762129     765902     996950     874977   
21        NaN        NaN        NaN        NaN        NaN        NaN   
22        NaN       2022        NaN        NaN        NaN       2023   
23         Q4         Q1         Q2         Q3         Q4         Q1   
24      50292      28161      27863      43686      91711      64621   
25     106791     102249      96623      82917     173419      85810   
26     157082     130410     124485     126602     265130     150432   
27     253633     116987     172561     192777     260814     168639   
28     410716     247397     297046     319380     525944     319070   
29     115137      88604     128558      93567     179644     149288   
30     452345     290540     336524     352956     291363     406619   
31     567482     379144     465082     446523     471006     555906   
32     978198     626541     762129     765902     996950     874977   

   Unnamed: 10 Unnamed: 11 Unnamed: 12  
0          NaN         NaN         NaN  
1           Q2          Q3          Q4  
2       916610      744888      883215  
3       455940      362262      404156  
4       460670      382626      479059  
5       195881      166597      225678  
6       264789      216030      253381  
7         4909        4101       12799  
8       259880      211929      240582  
9        77964       63579       72175  
10      181916      148350      168408  
11         NaN         NaN         NaN  
12         NaN         NaN         NaN  
13         NaN         NaN         NaN  
14          Q2          Q3          Q4  
15       55170       65167      136867  
16      139965      127152      153258  
17       46134       42307       62151  
18      241270      234625      352276  
19      681385      581578      523843  
20      922655      816203      876119  
21         NaN         NaN         NaN  
22         NaN         NaN         NaN  
23          Q2          Q3          Q4  
24       76721       44534       44532  
25      160293      144499      149466  
26      237014      189033      193998  
27      326490      177712      286847  
28      563504      366745      480844  
29      111183       89138      104565  
30      247967      360320      290710  
31      359151      449458      395274  
32      922655      816203      876119  

Section 3: Loading Sheets¶

After refining and adjusting the values, we are now ready to proceed with the analysis.

In the file provided below, you will find THREE SHEETS:

DATA SHEET: This sheet contains all the measures and adjustments made.¶
In [68]:
import pandas as pd

# Load the data from the Excel file
data = pd.read_excel("D:/Project/Measures.xlsx", sheet_name=0)

# Display the data
print(data)
   Company   Year  Current Ratio   Quick Ratio  Debt-To-equity  \
0     TECH  21-Q1        1.601955     1.192270        0.656674   
1     TECH  21-Q2        1.706546     1.335776        0.422728   
2     TECH  21-Q3        1.216049     0.862321        0.464594   
3     TECH  21-Q4        1.913652     1.484716        0.838244   
4     TECH  22-Q1        1.226503     0.953219        0.666313   
5     TECH  22-Q2        1.781697     1.508492        1.302581   
6     TECH  22-Q3        1.714383     1.332940        0.561375   
7     TECH  22-Q4        1.484683     1.030138        0.852647   
8     TECH  23-Q1        1.423941     0.954312        0.937875   
9     TECH  23-Q2        2.140346     1.773355        0.503257   
10    TECH  23-Q3        1.685500     1.461895        0.525862   
11    TECH  23-Q4        1.159676     0.713995        1.913341   
12   SPEED  21-Q1        2.122357     1.714540        0.378633   
13   SPEED  21-Q2        1.204955     0.832684        0.766396   
14   SPEED  21-Q3        1.873834     1.512217        0.560212   
15   SPEED  21-Q4        1.485705     1.139537        0.576812   
16   SPEED  22-Q1        1.312372     0.986694        0.825514   
17   SPEED  22-Q2        1.822953     1.470125        0.642396   
18   SPEED  22-Q3        0.996228     0.767370        1.383134   
19   SPEED  22-Q4        1.926791     1.618762        0.643407   
20   SPEED  23-Q1        1.870212     1.482688        0.656633   
21   SPEED  23-Q2        1.388476     1.120685        1.189466   
22   SPEED  23-Q3        1.171948     0.893504        0.495233   
23   SPEED  23-Q4        1.693602     1.492860        0.994678   
24   MACLR  21-Q1        2.372645     1.895140        0.857454   
25   MACLR  21-Q2        1.613854     1.142910        1.561394   
26   MACLR  21-Q3        1.679006     1.127258        1.579842   
27   MACLR  21-Q4        1.306410     1.033678        0.621083   
28   MACLR  22-Q1        2.372645     1.895140        0.857454   
29   MACLR  22-Q2        1.613854     1.142910        1.561394   
30   MACLR  22-Q3        1.679006     1.127258        1.579842   
31   MACLR  22-Q4        1.306410     1.033678        0.621083   
32   MACLR  23-Q1        1.233658     1.030119        1.727030   
33   MACLR  23-Q2        1.484086     1.189930        0.424839   
34   MACLR  23-Q3        2.395729     1.828592        0.494784   
35   MACLR  23-Q4        1.588970     1.278611        0.902473   
36    DAIN  21-Q1        1.261062     0.895646        1.255620   
37    DAIN  21-Q2        2.632908     2.118932        0.757378   
38    DAIN  21-Q3        1.632742     1.119672        1.027398   
39    DAIN  21-Q4        1.798064     1.258907        0.635128   
40    DAIN  22-Q1        1.649278     1.394617        0.578238   
41    DAIN  22-Q2        1.727993     1.425670        0.578787   
42    DAIN  22-Q3        2.235688     1.706809        0.617424   
43    DAIN  22-Q4        1.071302     0.792345        0.921925   
44    DAIN  23-Q1        1.723171     1.468213        0.457719   
45    DAIN  23-Q2        1.017955     0.823306        1.355371   
46    DAIN  23-Q3        1.241189     1.017384        0.716888   
47    DAIN  23-Q4        1.815876     1.495506        1.103821   

    Debt-To-Assets  Debt-To-Captial  Interest-Coverage  Recivables Turnover  \
0         0.358858         0.396381          50.672889            19.278062   
1         0.282206         0.297125          39.493899             9.088964   
2         0.299202         0.317217          11.505392            10.684338   
3         0.426227         0.456003          45.372347             7.018851   
4         0.378731         0.399873          38.886484             5.887098   
5         0.511058         0.565705          14.326655             8.576541   
6         0.331704         0.359539          18.581376             4.061877   
7         0.414178         0.460232          28.094004             5.088959   
8         0.448020         0.483971          23.307836            13.707893   
9         0.314390         0.334778          45.903590             6.918807   
10        0.325810         0.344633          21.587640             3.641444   
11        0.591525         0.656751           8.621754            10.730085   
12        0.255509         0.274644          13.262235            10.517332   
13        0.403480         0.433875          61.943595             6.637011   
14        0.330986         0.359061           5.681175             4.393714   
15        0.342205         0.365809          31.506403             4.823783   
16        0.426589         0.452209          46.444359            12.174268   
17        0.369968         0.391133          10.470037             7.920883   
18        0.518382         0.580384          28.681243             4.392437   
19        0.368339         0.391508          12.591810             8.036712   
20        0.367218         0.396366          43.621786             7.163237   
21        0.507692         0.543268          39.207627             5.409235   
22        0.310756         0.331208          21.807431             6.244762   
23        0.470782         0.498666          23.615371            11.567185   
24        0.434704         0.461629           9.510292            18.782273   
25        0.560576         0.609588          28.768225             6.939363   
26        0.557633         0.612379          20.490661             7.538726   
27        0.350937         0.383128          26.421022             8.548853   
28        0.434704         0.461629          23.286809             9.974034   
29        0.560576         0.609588          12.186778             5.737884   
30        0.557633         0.612379          17.157244             7.530281   
31        0.350937         0.383128          37.153285             5.898808   
32        0.573784         0.633301          26.761151             7.433316   
33        0.273990         0.298166          63.151231             6.243569   
34        0.314401         0.331007          15.564773             8.838398   
35        0.434925         0.474368          12.961020             3.662031   
36        0.495280         0.556663           9.581963            14.714018   
37        0.403671         0.430970          30.848627             6.551798   
38        0.459220         0.506757          25.667128             7.754997   
39        0.368457         0.388427          13.226807             7.324846   
40        0.349914         0.366382          73.164025            13.501735   
41        0.353200         0.366602          17.725746             5.608988   
42        0.359960         0.381733          14.284720             6.419717   
43        0.435561         0.479688          19.031004             6.536110   
44        0.290806         0.313997          32.143329             6.223619   
45        0.527589         0.575438          53.942473             5.618878   
46        0.394768         0.417551          52.679861             7.208769   
47        0.498006         0.524674          19.796856             4.860352   

           DSO  ...  Tax Burden  Interest Burden  Net Profit Margin  \
0    18.933438  ...         0.7         0.980266           0.099210   
1    40.158594  ...         0.7         0.974680           0.213061   
2    34.162155  ...         0.7         0.913084           0.111408   
3    52.002810  ...         0.7         0.977960           0.178860   
4    61.999984  ...         0.7         0.974284           0.151528   
5    42.557948  ...         0.7         0.930200           0.148514   
6    89.859944  ...         0.7         0.946183           0.179562   
7    71.723898  ...         0.7         0.964405           0.133408   
8    26.626995  ...         0.7         0.957096           0.116720   
9    52.754760  ...         0.7         0.978215           0.125466   
10  100.234952  ...         0.7         0.953677           0.205220   
11   34.016506  ...         0.7         0.884014           0.069191   
12   34.704618  ...         0.7         1.388873           0.138747   
13   54.994636  ...         0.7         0.573913           0.113416   
14   83.073228  ...         0.7         0.823980           0.076486   
15   75.666756  ...         0.7         0.968260           0.176854   
16   29.981268  ...         0.7         0.978469           0.171617   
17   46.080719  ...         0.7         0.904489           0.108674   
18   83.097380  ...         0.7         0.965134           0.139848   
19   45.416582  ...         0.7         0.920583           0.166765   
20   50.954615  ...         0.7         0.977076           0.133133   
21   67.477190  ...         0.7         0.974495           0.257176   
22   58.448988  ...         0.7         0.954144           0.220734   
23   31.554781  ...         0.7         0.957655           0.221394   
24   19.433218  ...         0.7         1.068670           0.121976   
25   52.598484  ...         0.7         0.915263           0.140071   
26   48.416670  ...         0.7         0.951197           0.083785   
27   42.695785  ...         0.7         0.962151           0.119905   
28   36.595024  ...         0.7         0.957057           0.161935   
29   63.612297  ...         0.7         0.917944           0.120855   
30   48.470967  ...         0.7         0.941716           0.129594   
31   61.876909  ...         0.7         0.973084           0.223587   
32   49.103253  ...         0.7         0.962632           0.177020   
33   58.460149  ...         0.7         0.984165           0.261627   
34   41.297076  ...         0.7         0.935752           0.107895   
35   99.671468  ...         0.7         0.922846           0.194325   
36   24.806277  ...         0.7         0.875544           0.098729   
37   55.709901  ...         0.7         1.008644           0.162196   
38   47.066429  ...         0.7         0.961040           0.086032   
39   49.830398  ...         0.7         0.924396           0.170024   
40   27.033563  ...         0.7         0.986332           0.205109   
41   65.074122  ...         0.7         0.943585           0.183961   
42   56.856086  ...         0.7         0.929995           0.133019   
43   55.843611  ...         0.7         0.947454           0.110156   
44   58.647550  ...         0.7         0.968889           0.190130   
45   64.959585  ...         0.7         0.981462           0.198466   
46   50.632779  ...         0.7         0.981017           0.199158   
47   75.097435  ...         0.7         0.949487           0.190676   

    Asset-Turnover  Return-On-Assets  Equity-Multiplier  Return-On-Equity  \
0         0.954901          0.094736           1.829896          0.173357   
1         0.832061          0.150004           1.497939          0.265555   
2         0.818896          0.102314           1.552774          0.141662   
3         0.921947          0.329799           1.966663          0.324302   
4         0.983122          0.186912           1.759329          0.262087   
5         0.973603          0.108235           2.548792          0.368540   
6         0.737109          0.129814           1.692395          0.224000   
7         0.996901          0.265990           2.058649          0.273790   
8         0.838587          0.100489           2.093380          0.204899   
9         0.824315          0.080105           1.600742          0.165554   
10        0.761102          0.199279           1.614016          0.252098   
11        1.126832          0.155934           3.234592          0.252191   
12        0.706925          0.098084           1.481877          0.145348   
13        0.872169          0.129328           1.899463          0.187890   
14        0.925852          0.053751           1.692555          0.119858   
15        0.808246          0.285883           1.685577          0.240939   
16        0.943353          0.148845           1.935148          0.313292   
17        0.705306          0.072576           1.736355          0.133088   
18        1.119750          0.161776           2.668176          0.417822   
19        0.791445          0.263971           1.746778          0.230549   
20        0.724728          0.103648           1.788128          0.172528   
21        0.990513          0.205831           2.342889          0.596818   
22        0.893231          0.261404           1.593639          0.314212   
23        1.015157          0.449499           2.112823          0.474856   
24        0.977278          0.119204           1.972501          0.235130   
25        1.115213          0.156209           2.785336          0.435096   
26        1.140932          0.095593           2.833119          0.270826   
27        0.876735          0.105125           1.769784          0.186049   
28        1.055243          0.170880           1.972501          0.337062   
29        1.602120          0.193624           2.785336          0.539308   
30        0.787256          0.102023           2.833119          0.289045   
31        1.219213          0.272600           1.769784          0.482443   
32        1.504601          0.266345           3.009897          0.801670   
33        0.602879          0.157729           1.550562          0.244569   
34        0.651722          0.070318           1.573733          0.110661   
35        0.861042          0.167322           2.075009          0.347194   
36        1.126347          0.111203           2.535173          0.281918   
37        0.971145          0.157516           1.876227          0.295536   
38        1.046158          0.090003           2.237268          0.201361   
39        0.992300          0.168715           1.723751          0.290823   
40        0.818175          0.167815           1.652514          0.277317   
41        0.737471          0.135666           1.638696          0.222315   
42        0.901413          0.119905           1.715260          0.205669   
43        0.895253          0.098618           2.116639          0.208738   
44        0.898818          0.170892           1.573964          0.268978   
45        0.993449          0.197166           2.568990          0.506518   
46        0.912626          0.181757           1.815972          0.330065   
47        1.008099          0.192220           2.216482          0.426053   

    Return-On-Total-Capital  Operating-Return-On-Assets  Fixed-Asset-Turnover  
0                  0.252726                    0.276123              1.462910  
1                  0.280123                    0.259832              1.106079  
2                  0.174101                    0.164699              1.049286  
3                  0.225253                    0.211619              1.634156  
4                  0.256469                    0.241484              1.338718  
5                  0.177803                    0.165505              1.752213  
6                  0.273197                    0.250086              1.107551  
7                  0.220395                    0.200789              1.533395  
8                  0.161090                    0.131930              1.199540  
9                  0.157699                    0.147011              1.381596  
10                 0.304071                    0.286724              1.084846  
11                 0.098191                    0.091239              1.985213  
12                 0.166591                    0.201774              1.037250  
13                 0.289817                    0.269564              1.229733  
14                 0.091713                    0.085029              1.574457  
15                 0.281326                    0.261714              1.060302  
16                 0.206987                    0.194301              1.356128  
17                 0.138473                    0.130819              1.043507  
18                 0.265870                    0.244103              1.653507  
19                 0.216152                    0.198040              1.149076  
20                 0.143662                    0.137767              1.066128  
21                 0.371655                    0.345709              1.404220  
22                 0.375640                    0.351878              1.134966  
23                 0.240409                    0.226037              1.740318  
24                 0.249436                    0.318698              1.559811  
25                 0.247449                    0.230435              1.953816  
26                 0.182138                    0.166544              1.896103  
27                 0.167141                    0.152639              1.322784  
28                 0.251786                    0.233593              1.684248  
29                 0.305822                    0.284794              2.806858  
30                 0.196348                    0.179537              1.308333  
31                 0.428545                    0.391360              1.839501  
32                 0.364027                    0.344767              2.463499  
33                 0.331124                    0.302829              0.797948  
34                 0.099943                    0.093180              0.950355  
35                 0.283234                    0.264260              1.224875  
36                 0.291456                    0.362886              1.925946  
37                 0.265454                    0.242965              1.756260  
38                 0.173366                    0.159241              1.626932  
39                 0.291300                    0.270377              1.395128  
40                 0.199551                    0.189795              1.245862  
41                 0.234929                    0.225451              1.027474  
42                 0.193712                    0.184642              1.429806  
43                 0.182177                    0.168184              1.251933  
44                 0.249378                    0.235552              1.277199  
45                 0.319738                    0.294598              1.345216  
46                 0.267099                    0.248473              1.280806  
47                 0.316084                    0.299448              1.686030  

[48 rows x 27 columns]
CHANGES SHEET: This sheet includes each measure along with the percentage change compared to the previous period.¶
In [69]:
import pandas as pd

# Load the data from the second sheet of the Excel file
changes = pd.read_excel("D:/Project/Measures.xlsx", sheet_name=1)

# Display the data
print(changes)
   Company    CHANGES %  Current Ratio   Quick Ratio  Debt-To-equity  \
0     Tech        21-Q1        0.000000     0.000000        0.000000   
1     Tech  21-Q2/21-Q1        0.065289     0.120363       -0.356259   
2     Tech  21-Q3/21-Q2       -0.287421    -0.354442        0.099037   
3     Tech  21-Q4/21-Q3        0.573664     0.721768        0.804252   
4     Tech  22-Q1/21-Q4       -0.359077    -0.357979       -0.205109   
5     Tech  22-Q2/22-Q1        0.452664     0.582525        0.954909   
6     Tech  22-Q3/22-Q2       -0.037781    -0.116376       -0.569029   
7     Tech  22-Q4/22-Q3       -0.133984    -0.227169        0.518856   
8     Tech  23-Q1/22-Q4       -0.040912    -0.073607        0.099957   
9     Tech  23-Q2/23-Q1        0.503115     0.858254       -0.463407   
10    Tech  23-Q3/23-Q2       -0.212510    -0.175633        0.044916   
11    Tech  23-Q4/23-Q3       -0.311969    -0.511596        2.638486   
12   Speed        21-Q1        0.000000     0.000000        0.000000   
13   Speed  21-Q2/21-Q1       -0.432256    -0.514340        1.024110   
14   Speed  21-Q3/21-Q2        0.555106     0.816076       -0.269031   
15   Speed  21-Q4/21-Q3       -0.207131    -0.246446        0.029633   
16   Speed  22-Q1/21-Q4       -0.116667    -0.134127        0.431165   
17   Speed  22-Q2/22-Q1        0.389052     0.489949       -0.221823   
18   Speed  22-Q3/22-Q2       -0.453508    -0.478024        1.153086   
19   Speed  22-Q4/22-Q3        0.934085     1.109493       -0.534819   
20   Speed  23-Q1/22-Q4       -0.029364    -0.084061        0.020556   
21   Speed  23-Q2/23-Q1       -0.257584    -0.244153        0.811464   
22   Speed  23-Q3/23-Q2       -0.155946    -0.202717       -0.583651   
23   Speed  23-Q4/23-Q3        0.445117     0.670794        1.008506   
24   Maclr        21-Q1        0.000000     0.000000        0.000000   
25   Maclr  21-Q2/21-Q1       -0.319808    -0.396926        0.820964   
26   Maclr  21-Q3/21-Q2        0.040370    -0.013695        0.011815   
27   Maclr  21-Q4/21-Q3       -0.221915    -0.083016       -0.606870   
28   Maclr  22-Q1/21-Q4        0.816157     0.833396        0.380579   
29   Maclr  22-Q2/22-Q1       -0.319808    -0.396926        0.820964   
30   Maclr  22-Q3/22-Q2        0.040370    -0.013695        0.011815   
31   Maclr  22-Q4/22-Q3       -0.221915    -0.083016       -0.606870   
32   Maclr  23-Q1/22-Q4       -0.055688    -0.003443        1.780674   
33   Maclr  23-Q2/23-Q1        0.202996     0.155139       -0.754006   
34   Maclr  23-Q3/23-Q2        0.614279     0.536722        0.164639   
35   Maclr  23-Q4/23-Q3       -0.336749    -0.300768        0.823973   
36    Dain        21-Q1        0.000000     0.000000        0.000000   
37    Dain  21-Q2/21-Q1        1.087849     1.365813       -0.396809   
38    Dain  21-Q3/21-Q2       -0.379871    -0.471587        0.356519   
39    Dain  21-Q4/21-Q3        0.101254     0.124354       -0.381809   
40    Dain  22-Q1/21-Q4       -0.082748     0.107800       -0.089573   
41    Dain  22-Q2/22-Q1        0.047727     0.022266        0.000949   
42    Dain  22-Q3/22-Q2        0.293806     0.197198        0.066756   
43    Dain  22-Q4/22-Q3       -0.520818    -0.535774        0.493179   
44    Dain  23-Q1/22-Q4        0.608482     0.852998       -0.503518   
45    Dain  23-Q2/23-Q1       -0.409255    -0.439246        1.961143   
46    Dain  23-Q3/23-Q2        0.219297     0.235729       -0.471076   
47    Dain  23-Q4/23-Q3        0.463013     0.469953        0.539741   

    Debt-To-Assets  Debt-To-Captial  Interest-Coverage  Recivables Turnover  \
0         0.000000         0.000000           0.000000             0.000000   
1        -0.213600        -0.250405          -0.220611            -0.528533   
2         0.060226         0.067621          -0.708679             0.175529   
3         0.424543         0.437511           2.943572            -0.343071   
4        -0.111433        -0.123092          -0.142947            -0.161245   
5         0.349396         0.414713          -0.631578             0.456837   
6        -0.350946        -0.364441           0.296979            -0.526397   
7         0.248637         0.280062           0.511944             0.252859   
8         0.081707         0.051581          -0.170363             1.693653   
9        -0.298267        -0.308268           0.969449            -0.495268   
10        0.036323         0.029437          -0.529718            -0.473689   
11        0.815553         0.905656          -0.600616             1.946656   
12        0.000000         0.000000           0.000000             0.000000   
13        0.579121         0.579774           3.670675            -0.368945   
14       -0.179673        -0.172432          -0.908285            -0.337998   
15        0.033895         0.018793           4.545755             0.097883   
16        0.246592         0.236188           0.474124             1.523801   
17       -0.132730        -0.135061          -0.774568            -0.349375   
18        0.401152         0.483853           1.739364            -0.445461   
19       -0.289444        -0.325433          -0.560974             0.829670   
20       -0.003044         0.012408           2.464298            -0.108686   
21        0.382537         0.370622          -0.101192            -0.244862   
22       -0.387905        -0.390341          -0.443796             0.154463   
23        0.514956         0.505598           0.082905             0.852302   
24        0.000000         0.000000           0.000000             0.000000   
25        0.289559         0.320515           2.024957            -0.630537   
26       -0.005250         0.004580          -0.287733             0.086371   
27       -0.370667        -0.374361           0.289418             0.133992   
28        0.238695         0.204893          -0.118626             0.166710   
29        0.289559         0.320515          -0.476666            -0.424718   
30       -0.005250         0.004580           0.407857             0.312379   
31       -0.370667        -0.374361           1.165458            -0.216655   
32        0.635003         0.652972          -0.279710             0.260139   
33       -0.522485        -0.529187           1.359810            -0.160056   
34        0.147490         0.110142          -0.753532             0.415600   
35        0.383342         0.433106          -0.167285            -0.585668   
36        0.000000         0.000000           0.000000             0.000000   
37       -0.184964        -0.225796           2.219447            -0.554724   
38        0.137610         0.175851          -0.167965             0.183644   
39       -0.197646        -0.233504          -0.484679            -0.055468   
40       -0.050326        -0.056755           4.531496             0.843279   
41        0.009389         0.000601          -0.757726            -0.584573   
42        0.019140         0.041273          -0.194126             0.144541   
43        0.210027         0.256607           0.332263             0.018131   
44       -0.332340        -0.345415           0.688998            -0.047810   
45        0.814227         0.832626           0.678186            -0.097169   
46       -0.251751        -0.274378          -0.023407             0.282955   
47        0.261515         0.256552          -0.624204            -0.325772   

         DSO  ...    Tax Burden  Interest Burden  Net Profit Margin  \
0   0.000000  ...  0.000000e+00         0.000000           0.000000   
1   1.121041  ... -1.586033e-16        -0.005698           1.147574   
2  -0.149319  ...  1.586033e-16        -0.063196          -0.477106   
3   0.522234  ... -1.586033e-16         0.071051           0.605445   
4   0.192243  ...  1.586033e-16        -0.003759          -0.152815   
5  -0.313581  ...  0.000000e+00        -0.045248          -0.019887   
6   1.111473  ...  0.000000e+00         0.017182           0.209055   
7  -0.201826  ... -1.586033e-16         0.019259          -0.257033   
8  -0.628757  ...  0.000000e+00        -0.007579          -0.125094   
9   0.981251  ...  1.586033e-16         0.022066           0.074932   
10  0.900017  ... -1.586033e-16        -0.025084           0.635661   
11 -0.660632  ...  0.000000e+00        -0.073047          -0.662843   
12  0.000000  ...  0.000000e+00         0.000000           0.000000   
13  0.584649  ...  0.000000e+00        -0.586778          -0.182570   
14  0.510570  ...  1.586033e-16         0.435722          -0.325613   
15 -0.089156  ... -1.586033e-16         0.175102           1.312238   
16 -0.603772  ...  1.586033e-16         0.010543          -0.029610   
17  0.536984  ... -1.586033e-16        -0.075607          -0.366767   
18  0.803300  ...  0.000000e+00         0.067048           0.286861   
19 -0.453454  ...  0.000000e+00        -0.046160           0.192473   
20  0.121939  ...  0.000000e+00         0.061366          -0.201672   
21  0.324261  ...  1.586033e-16        -0.002641           0.931720   
22 -0.133796  ... -1.586033e-16        -0.020883          -0.141700   
23 -0.460131  ...  0.000000e+00         0.003679           0.002990   
24  0.000000  ...  0.000000e+00         0.000000           0.000000   
25  1.706628  ...  0.000000e+00        -0.143549           0.148355   
26 -0.079504  ...  0.000000e+00         0.039261          -0.401840   
27 -0.118159  ...  0.000000e+00         0.011516           0.431103   
28 -0.142889  ... -1.586033e-16        -0.005295           0.350524   
29  0.738277  ...  0.000000e+00        -0.040868          -0.253681   
30 -0.238025  ...  1.586033e-16         0.025897           0.072307   
31  0.276577  ...  0.000000e+00         0.033310           0.725291   
32 -0.206437  ...  0.000000e+00        -0.010741          -0.208271   
33  0.190556  ... -1.586033e-16         0.022368           0.477949   
34 -0.293586  ...  1.586033e-16        -0.049192          -0.587600   
35  1.413524  ...  0.000000e+00        -0.013793           0.801057   
36  0.000000  ...  0.000000e+00         0.000000           0.000000   
37  1.245799  ...  1.586033e-16         0.152019           0.642847   
38 -0.155151  ...  0.000000e+00        -0.047196          -0.469582   
39  0.058725  ...  0.000000e+00        -0.038129           0.976294   
40 -0.457489  ... -1.586033e-16         0.067002           0.206354   
41  1.407160  ...  0.000000e+00        -0.043340          -0.103106   
42 -0.126287  ...  1.586033e-16        -0.014402          -0.276918   
43 -0.017808  ... -1.586033e-16         0.018773          -0.171878   
44  0.050211  ...  0.000000e+00         0.022624           0.726001   
45  0.107627  ...  0.000000e+00         0.012976           0.043848   
46 -0.220550  ...  0.000000e+00        -0.000453           0.003484   
47  0.483178  ...  1.586033e-16        -0.032141          -0.042590   

    Asset-Turnover  Return-On-Assets  Equity-Multiplier  Return-On-Equity  \
0         0.000000          0.000000           0.000000          0.000000   
1        -0.128642          0.583386          -0.181408          0.531837   
2        -0.015822         -0.317923           0.036607         -0.466541   
3         0.125841          2.223398           0.266548          1.289255   
4         0.066355         -0.433256          -0.105424         -0.191840   
5        -0.009682         -0.420931           0.448730          0.406170   
6        -0.242906          0.199380          -0.336001         -0.392196   
7         0.352447          1.049002           0.216411          0.222279   
8        -0.158806         -0.622207           0.016871         -0.251618   
9        -0.017019         -0.202850          -0.235331         -0.192022   
10       -0.076685          1.487720           0.008292          0.522753   
11        0.480526         -0.217510           1.004065          0.000367   
12        0.000000          0.000000           0.000000          0.000000   
13        0.233750          0.318554           0.281795          0.292696   
14        0.061551         -0.584383          -0.108930         -0.362086   
15       -0.127024          4.318646          -0.004122          1.010206   
16        0.167161         -0.479349           0.148063          0.300297   
17       -0.252342         -0.512406          -0.102728         -0.575194   
18        0.587609          1.229052           0.536654          2.139433   
19       -0.293195          0.631707          -0.345329         -0.448213   
20       -0.084299         -0.607349           0.023672         -0.251665   
21        0.366738          0.985857           0.310246          2.459253   
22       -0.098213          0.269994          -0.319797         -0.473521   
23        0.136499          0.719558           0.325785          0.511258   
24        0.000000          0.000000           0.000000          0.000000   
25        0.141143          0.310437           0.412084          0.850446   
26        0.023061         -0.388046           0.017155         -0.377547   
27       -0.231562          0.099714          -0.375323         -0.313034   
28        0.203605          0.625497           0.114543          0.811687   
29        0.518247          0.133098           0.412084          0.600028   
30       -0.508616         -0.473085           0.017155         -0.464046   
31        0.548686          1.671935          -0.375323          0.669096   
32        0.234076         -0.022946           0.700715          0.661690   
33       -0.599310         -0.407800          -0.484846         -0.694926   
34        0.081016         -0.554189           0.014944         -0.547527   
35        0.321180          1.379520           0.318526          2.137459   
36        0.000000          0.000000           0.000000          0.000000   
37       -0.137792          0.416475          -0.259922          0.048303   
38        0.077242         -0.428611           0.192429         -0.318660   
39       -0.051482          0.874550          -0.229529          0.444287   
40       -0.175476         -0.005332          -0.041327         -0.046439   
41       -0.098640         -0.191575          -0.008362         -0.198335   
42        0.222304         -0.116174           0.046722         -0.074879   
43       -0.006834         -0.177538           0.234005          0.014923   
44        0.003982          0.732875          -0.256385          0.288592   
45        0.105283          0.153748           0.632179          0.883123   
46       -0.081356         -0.078155          -0.293118         -0.348365   
47        0.104614          0.057568           0.220549          0.290814   

    Return-On-Total-Capital  Operating-Return-On-Assets  Fixed-Asset-Turnover  
0                  0.000000                    0.000000              0.000000  
1                  0.108404                   -0.059000             -0.243919  
2                 -0.378482                   -0.366132             -0.051346  
3                  0.293804                    0.284885              0.557397  
4                  0.138579                    0.141122             -0.180789  
5                 -0.306726                   -0.314632              0.308874  
6                  0.536515                    0.511044             -0.367913  
7                 -0.193273                   -0.197118              0.384491  
8                 -0.269087                   -0.342940             -0.217723  
9                 -0.021051                    0.114310              0.151772  
10                 0.928178                    0.950350             -0.214788  
11                -0.677077                   -0.681787              0.829949  
12                 0.000000                    0.000000              0.000000  
13                 0.739689                    0.335969              0.185571  
14                -0.683549                   -0.684566              0.280324  
15                 2.067464                    2.077918             -0.326561  
16                -0.264243                   -0.257583              0.279002  
17                -0.331005                   -0.326722             -0.230525  
18                 0.920007                    0.865964              0.584568  
19                -0.187002                   -0.188703             -0.305068  
20                -0.335364                   -0.304346             -0.072186  
21                 1.587005                    1.509374              0.317121  
22                 0.010723                    0.017843             -0.191746  
23                -0.360001                   -0.357627              0.533365  
24                 0.000000                    0.000000              0.000000  
25                -0.007966                   -0.276948              0.252598  
26                -0.263936                   -0.277264             -0.029538  
27                -0.082339                   -0.083493             -0.302367  
28                 0.506427                    0.530369              0.273260  
29                 0.214608                    0.219188              0.666535  
30                -0.357966                   -0.369592             -0.533880  
31                 1.182581                    1.179836              0.405988  
32                -0.150553                   -0.119054              0.339221  
33                -0.090386                   -0.121642             -0.676091  
34                -0.698171                   -0.692301              0.190998  
35                 1.833969                    1.836013              0.288861  
36                 0.000000                    0.000000              0.000000  
37                -0.089214                   -0.330464             -0.088106  
38                -0.346908                   -0.344594             -0.073638  
39                 0.680265                    0.697913             -0.142479  
40                -0.314964                   -0.298035             -0.106991  
41                 0.177286                    0.187863             -0.175291  
42                -0.175441                   -0.181009              0.391574  
43                -0.059552                   -0.089133             -0.124404  
44                 0.368881                    0.400559              0.020182  
45                 0.282142                    0.250670              0.053255  
46                -0.164632                   -0.156568             -0.047881  
47                 0.183395                    0.205153              0.316382  

[48 rows x 27 columns]
RATIOS SHEET: This sheet contains the measures for each quarter of the year for each company Which is comparable:¶

Which Contain Four Main Measures which we will discuss now:

Section 4: Graph & Analysis¶

First: Turnover Measures_1¶

In [70]:
import pandas as pd
import plotly.express as px
import plotly.graph_objects as go

# Define the data
quarters = ['21-Q1', '21-Q2', '21-Q3', '21-Q4', 
            '22-Q1', '22-Q2', '22-Q3', '22-Q4', 
            '23-Q1', '23-Q2', '23-Q3', '23-Q4']

# Data for each measure
company_tech_receivable = [19.28, 9.09, 10.68, 7.02, 5.89, 8.58, 4.06, 5.09, 13.71, 6.92, 3.64, 10.73]
company_speed_receivable = [10.52, 6.64, 4.39, 4.82, 12.17, 7.92, 4.39, 8.04, 7.16, 5.41, 6.24, 11.57]
company_maclr_receivable = [18.78, 6.94, 7.54, 8.55, 9.97, 5.74, 7.53, 5.90, 7.43, 6.24, 8.84, 3.66]
company_dain_receivable = [14.71, 6.55, 7.75, 7.32, 13.50, 5.61, 6.42, 6.54, 6.22, 5.62, 7.21, 4.86]

company_tech_payable = [11.36, 5.30, 10.47, 7.01, 9.05, 5.63, 5.73, 7.80, 12.32, 15.27, 7.33, 6.88]
company_speed_payable = [10.30, 8.03, 5.02, 6.35, 6.10, 7.59, 7.27, 4.50, 11.34, 12.63, 7.23, 5.08]
company_maclr_payable = [18.05, 8.65, 9.21, 5.30, 6.40, 11.97, 5.71, 6.08, 15.92, 6.01, 4.62, 6.31]
company_dain_payable = [11.10, 6.96, 8.95, 6.96, 5.47, 9.59, 11.25, 9.87, 10.72, 11.89, 5.98, 9.08]

company_tech_inventory = [12.11, 5.39, 9.49, 5.58, 7.46, 6.80, 7.25, 6.58, 4.47, 5.81, 8.76, 6.85]
company_speed_inventory = [11.73, 7.30, 4.24, 6.86, 5.87, 6.11, 8.66, 6.43, 7.04, 6.18, 8.61, 6.05]
company_maclr_inventory = [14.01, 6.02, 6.14, 4.54, 6.37, 8.32, 3.80, 4.88, 9.64, 5.99, 5.24, 6.41]
company_dain_inventory = [10.17, 5.76, 7.14, 5.06, 3.64, 7.58, 7.69, 7.59, 6.17, 10.79, 8.19, 7.74]

company_tech_cash = [16.95, 39.09, 37.78, 65.33, 70.60, 31.40, 76.44, 80.36, 78.61, 91.63, 92.15, 34.22]
company_speed_cash = [30.39, 59.54, 96.43, 71.40, 32.30, 57.68, 75.05, 21.08, 70.64, 97.63, 50.35, 20.02]
company_maclr_cash = [25.27, 71.06, 68.26, 54.23, 36.85, 76.96, 80.50, 76.71, 64.05, 58.65, 32.01, 98.85]
company_dain_cash = [27.80, 66.60, 57.39, 69.60, 60.59, 75.15, 71.85, 66.98, 83.79, 68.07, 34.10, 82.05]

data_receivable = pd.DataFrame({
    'Quarter': quarters * 4,
    'Company': ['Tech']*len(quarters) + ['Speed']*len(quarters) + ['Maclr']*len(quarters) + ['Dain']*len(quarters),
    'Value': company_tech_receivable + company_speed_receivable + company_maclr_receivable + company_dain_receivable,
    'Measure': 'Receivable Turnover'
})

data_payable = pd.DataFrame({
    'Quarter': quarters * 4,
    'Company': ['Tech']*len(quarters) + ['Speed']*len(quarters) + ['Maclr']*len(quarters) + ['Dain']*len(quarters),
    'Value': company_tech_payable + company_speed_payable + company_maclr_payable + company_dain_payable,
    'Measure': 'Payable Turnover'
})

data_inventory = pd.DataFrame({
    'Quarter': quarters * 4,
    'Company': ['Tech']*len(quarters) + ['Speed']*len(quarters) + ['Maclr']*len(quarters) + ['Dain']*len(quarters),
    'Value': company_tech_inventory + company_speed_inventory + company_maclr_inventory + company_dain_inventory,
    'Measure': 'Inventory Turnover'
})

data_cash = pd.DataFrame({
    'Quarter': quarters * 4,
    'Company': ['Tech']*len(quarters) + ['Speed']*len(quarters) + ['Maclr']*len(quarters) + ['Dain']*len(quarters),
    'Value': company_tech_cash + company_speed_cash + company_maclr_cash + company_dain_cash,
    'Measure': 'Cash Conversion Cycle'
})

data = pd.concat([data_receivable, data_payable, data_inventory, data_cash], ignore_index=True)

# Create a numeric quarter column for trend lines
data['Quarter_Num'] = pd.Categorical(data['Quarter'], categories=quarters, ordered=True).codes

# Define custom color palette
my_palette = {
    "Tech": "darkslateblue", 
    "Speed": "lightseagreen", 
    "Maclr": "darkgoldenrod", 
    "Dain": "coral"
}

# Plot function
def plot_measure(data, measure, y_label):
    data_filtered = data[data['Measure'] == measure]
    
    fig = go.Figure()

    for company in data_filtered['Company'].unique():
        company_data = data_filtered[data_filtered['Company'] == company]
        fig.add_trace(go.Bar(
            x=company_data['Quarter'],
            y=company_data['Value'],
            name=company,
            marker_color=my_palette[company]
        ))
        fig.add_trace(go.Scatter(
            x=company_data['Quarter'],
            y=company_data['Value'],
            mode='lines',
            name=f'{company} Trend',
            line=dict(color=my_palette[company])
        ))

    fig.update_layout(
        title=f'{measure} Comparison',
        xaxis_title='Year-Quarter',
        yaxis_title=y_label,
        barmode='group'
    )

    return fig

# Plot Receivable Turnover
Receivable_Turnover = plot_measure(data, 'Receivable Turnover', 'Receivable Turnover')
Receivable_Turnover.show()

# Plot Payable Turnover
Payable_Turnover = plot_measure(data, 'Payable Turnover', 'Payable Turnover')
Payable_Turnover.show()

# Plot Inventory Turnover
Inventory_Turnover = plot_measure(data, 'Inventory Turnover', 'Inventory Turnover')
Inventory_Turnover.show()

# Plot Cash Conversion Cycle
CCC = plot_measure(data, 'Cash Conversion Cycle', 'Cash Conversion Cycle')
CCC.show()
Conclusions_1:¶

Speed: Speed's dominance has allowed it to impose strict controls on Accounts Receivable, enabling the company to collect its funds promptly at the end of each quarter. For instance, if a customer purchases on account for $100,000 at the beginning of Q1 2021, they have until the end of Q1 2022 to settle the amount due. This practice contributes to fluctuations in the Cash Conversion Cycle each year. These restrictions effectively enhance the company's liquidity and activity ratios, providing it with greater debt accessibility which enhances the Net_Profit_Margin.

Maclr: In contrast to the previous company, Maclr does not impose any restrictions on Accounts Receivables. This is evident from the low Receivable Turnover over the years, which has significantly impacted the Cash Conversion Cycle by extending it. This extension is detrimental to any commercial company aiming to shorten the cycle for quick cash collection and inventory purchases. Consequently, this has also negatively affected the Activity Ratios, showing a decline over the years.

Dain: Dain faces a problem similar to Maclr, as it does not appear to impose any restrictions on Accounts Receivables. The consequences of this have already been discussed in the case of Maclr. One significant difference is that Dain has a high inventory turnover, indicating it sells its products quickly, almost surpassing the dominant company, Speed. This efficiency has also helped Dain reduce its Cash Conversion Cycle. Additionally, Dain has managed to extend its Payable Turnover without adversely affecting its operations over the years. Therefore, its only issue lies in the Receivable Turnover.

Tech: The issue is the lack of restrictions on Accounts Receivables, despite stable inventory turnover rates and an increase in Payable Turnover. The lack of controls on Accounts Receivables has significantly extended the Cash Conversion Cycle due to the decrease in Receivable Turnover Ratio. This situation poses a greater risk to the company, particularly if bad debt and the Allowance for Doubtful Accounts increase, which could severely weaken its liquidity ratios.

Second: Activity Measures_2¶

In [71]:
import pandas as pd
import plotly.express as px
import plotly.graph_objects as go

# Define the data
quarters = ['21-Q1', '21-Q2', '21-Q3', '21-Q4', 
            '22-Q1', '22-Q2', '22-Q3', '22-Q4', 
            '23-Q1', '23-Q2', '23-Q3', '23-Q4']

# Current Ratio data
current_ratio_tech = [1.60, 1.71, 1.22, 1.91, 1.23, 1.78, 1.71, 1.48, 1.42, 2.14, 1.69, 1.16]
current_ratio_speed = [2.12, 1.20, 1.87, 1.49, 1.31, 1.82, 1.00, 1.93, 1.87, 1.39, 1.17, 1.69]
current_ratio_maclr = [2.37, 1.61, 1.68, 1.31, 2.37, 1.61, 1.68, 1.31, 1.23, 1.48, 2.40, 1.59]
current_ratio_dain = [1.26, 2.63, 1.63, 1.80, 1.65, 1.73, 2.24, 1.07, 1.72, 1.02, 1.24, 1.82]

# Quick Ratio data
quick_ratio_tech = [1.19, 1.34, 0.86, 1.48, 0.95, 1.51, 1.33, 1.03, 0.95, 1.77, 1.46, 0.71]
quick_ratio_speed = [1.71, 0.83, 1.51, 1.14, 0.99, 1.47, 0.77, 1.62, 1.48, 1.12, 0.89, 1.49]
quick_ratio_maclr = [1.90, 1.14, 1.13, 1.03, 1.90, 1.14, 1.13, 1.03, 1.03, 1.19, 1.83, 1.28]
quick_ratio_dain = [0.90, 2.12, 1.12, 1.26, 1.39, 1.43, 1.71, 0.79, 1.47, 0.82, 1.02, 1.50]

data_current = pd.DataFrame({
    'Quarter': quarters * 4,
    'Company': ['Tech']*len(quarters) + ['Speed']*len(quarters) + ['Maclr']*len(quarters) + ['Dain']*len(quarters),
    'Value': current_ratio_tech + current_ratio_speed + current_ratio_maclr + current_ratio_dain,
    'Measure': 'Current Ratio'
})

data_quick = pd.DataFrame({
    'Quarter': quarters * 4,
    'Company': ['Tech']*len(quarters) + ['Speed']*len(quarters) + ['Maclr']*len(quarters) + ['Dain']*len(quarters),
    'Value': quick_ratio_tech + quick_ratio_speed + quick_ratio_maclr + quick_ratio_dain,
    'Measure': 'Quick Ratio'
})

data = pd.concat([data_current, data_quick], ignore_index=True)

# Create a numeric quarter column for trend lines
data['Quarter_Num'] = pd.Categorical(data['Quarter'], categories=quarters, ordered=True).codes

# Define custom color palette
my_palette = {
    "Tech": "darkslateblue", 
    "Speed": "lightseagreen", 
    "Maclr": "darkgoldenrod", 
    "Dain": "coral"
}

# Plot function
def plot_measure(data, measure, y_label):
    data_filtered = data[data['Measure'] == measure]
    
    fig = go.Figure()

    for company in data_filtered['Company'].unique():
        company_data = data_filtered[data_filtered['Company'] == company]
        fig.add_trace(go.Bar(
            x=company_data['Quarter'],
            y=company_data['Value'],
            name=company,
            marker_color=my_palette[company]
        ))
        fig.add_trace(go.Scatter(
            x=company_data['Quarter'],
            y=company_data['Value'],
            mode='lines',
            name=f'{company} Trend',
            line=dict(color=my_palette[company])
        ))

    fig.update_layout(
        title=f'{measure} Comparison',
        xaxis_title='Year-Quarter',
        yaxis_title=y_label,
        barmode='group'
    )

    return fig

# Plot Current Ratio
Current_Ratio = plot_measure(data, 'Current Ratio', 'Current Ratio')
Current_Ratio.show()

# Plot Quick Ratio
Quick_Ratio = plot_measure(data, 'Quick Ratio', 'Quick Ratio')
Quick_Ratio.show()
Conclusions_2:¶

Speed: it is also on a slight downward trend and maintain more stability compared to Dain and Tech.

Maclr: It shows a slight downward trend but is relatively stable and maintain more stability compared to Dain and Tech.

Dain: It is on a downward trend and has the highest fluctuations in the Current Ratio, particularly in Q1 of 2021.

Tech: It exhibits a clear downward trend and a consistent decline, indicating potential liquidity issues.

Third: Solvency Measures_3¶

In [72]:
import pandas as pd
import plotly.express as px
import plotly.graph_objects as go

# Define the data
quarters = ['21-Q1', '21-Q2', '21-Q3', '21-Q4', 
            '22-Q1', '22-Q2', '22-Q3', '22-Q4', 
            '23-Q1', '23-Q2', '23-Q3', '23-Q4']

company_tech_debt_to_assets = [36, 28, 30, 43, 38, 51, 33, 41, 45, 31, 33, 59]
company_speed_debt_to_assets = [26, 40, 33, 34, 43, 37, 52, 37, 37, 51, 31, 47]
company_maclr_debt_to_assets = [43, 56, 56, 35, 43, 56, 56, 35, 57, 27, 31, 43]
company_dain_debt_to_assets = [50, 40, 46, 37, 35, 35, 36, 44, 29, 53, 39, 50]

company_tech_debt_to_equity = [66, 42, 46, 84, 67, 130, 56, 85, 94, 50, 53, 191]
company_speed_debt_to_equity = [38, 77, 56, 58, 83, 64, 138, 64, 66, 119, 50, 99]
company_maclr_debt_to_equity = [86, 156, 158, 62, 86, 156, 158, 62, 173, 42, 49, 90]
company_dain_debt_to_equity = [126, 76, 103, 64, 58, 58, 62, 92, 46, 136, 72, 110]

company_tech_debt_to_capital = [40, 30, 32, 46, 40, 57, 36, 46, 48, 33, 34, 66]
company_speed_debt_to_capital = [27, 43, 36, 37, 45, 39, 58, 39, 40, 54, 33, 50]
company_maclr_debt_to_capital = [46, 61, 61, 38, 46, 61, 61, 38, 63, 30, 33, 47]
company_dain_debt_to_capital = [56, 43, 51, 39, 37, 37, 38, 48, 31, 58, 42, 52]

data_debt_to_assets = pd.DataFrame({
    'Quarter': quarters,
    'Tech': company_tech_debt_to_assets,
    'Speed': company_speed_debt_to_assets,
    'Maclr': company_maclr_debt_to_assets,
    'Dain': company_dain_debt_to_assets,
    'Measure': 'Debt_To_Assets'
})

data_debt_to_equity = pd.DataFrame({
    'Quarter': quarters,
    'Tech': company_tech_debt_to_equity,
    'Speed': company_speed_debt_to_equity,
    'Maclr': company_maclr_debt_to_equity,
    'Dain': company_dain_debt_to_equity,
    'Measure': 'Debt_To_Equity'
})

data_debt_to_capital = pd.DataFrame({
    'Quarter': quarters,
    'Tech': company_tech_debt_to_capital,
    'Speed': company_speed_debt_to_capital,
    'Maclr': company_maclr_debt_to_capital,
    'Dain': company_dain_debt_to_capital,
    'Measure': 'Debt_To_Capital'
})

data = pd.concat([data_debt_to_assets, data_debt_to_equity, data_debt_to_capital], ignore_index=True)

# Reshape data from wide to long format
data = pd.melt(data, id_vars=['Quarter', 'Measure'], value_vars=['Tech', 'Speed', 'Maclr', 'Dain'], 
               var_name='Company', value_name='Value')

# Create a numeric quarter column for trend lines
data['Quarter_Num'] = pd.Categorical(data['Quarter'], categories=quarters, ordered=True).codes

# Define custom color palette
my_palette = {
    'Tech': 'darkslateblue', 
    'Speed': 'lightseagreen', 
    'Maclr': 'darkgoldenrod', 
    'Dain': 'coral'
}

# Plot function
def plot_measure(data, measure, y_label):
    data_filtered = data[data['Measure'] == measure]
    
    fig = go.Figure()

    for company in data_filtered['Company'].unique():
        company_data = data_filtered[data_filtered['Company'] == company]
        fig.add_trace(go.Bar(
            x=company_data['Quarter'],
            y=company_data['Value'],
            name=company,
            marker_color=my_palette[company]
        ))
        fig.add_trace(go.Scatter(
            x=company_data['Quarter'],
            y=company_data['Value'],
            mode='lines',
            name=f'{company} Trend',
            line=dict(color=my_palette[company])
        ))

    fig.update_layout(
        title=f'{measure} Comparison',
        xaxis_title='Year-Quarter',
        yaxis_title=y_label,
        barmode='group'
    )

    return fig

# Plot Debt-To-Assets
Debt_To_Assets = plot_measure(data, 'Debt_To_Assets', 'Debt-To-Assets (%)')
Debt_To_Assets.show()

# Plot Debt-To-Equity
Debt_To_Equity = plot_measure(data, 'Debt_To_Equity', 'Debt-To-Equity (%)')
Debt_To_Equity.show()

# Plot Debt-To-Capital
Debt_To_Capital = plot_measure(data, 'Debt_To_Capital', 'Debt-To-Capital (%)')
Debt_To_Capital.show()
Conclusions_3:¶

Speed: Regarding the company's debt, the Solvency Ratios indicate that the company leverages its market power and dominance to increase its borrowing capacity. This has significantly impacted the Net Profit Margin, as the higher level of debt has effectively reduced the company’s tax burden.

Maclr: Regarding Solvency, despite the cash flow issue, the company’s Net Profit Margin remains stable and does not decline. This is because the company heavily relies on external debt rather than operating cash flow and liquidity. Although this strategy has reduced taxes and increased net income, it places the company at high risk of bankruptcy if it fails to meet its debt obligations. This issue is further highlighted by the declining Profitability Ratios over the coming years, confirming the problem of reduced operating efficiency.

Dain: Regarding debt, Dain is similar to Maclr, except for the bankruptcy risk. Dain has sufficient liquidity from its operating activities to manage its debt obligations, especially short-term debts.

Tech: Although the company does not rely on debt to the same extent as others, except for the last quarter of the previous year, it still fails to show upward progress in Net Profit Margin trends. This lack of improvement will impact the stability of its operating systems

Finally: Financial_Health Measures_4,5¶

In [73]:
import pandas as pd
import plotly.express as px
import plotly.graph_objects as go

# Define the data
quarters = ['21-Q1', '21-Q2', '21-Q3', '21-Q4', 
            '22-Q1', '22-Q2', '22-Q3', '22-Q4', 
            '23-Q1', '23-Q2', '23-Q3', '23-Q4']

company_tech_gross = [44, 54, 40, 48, 51, 46, 47, 47, 45, 44, 55, 40]
company_speed_gross = [43, 41, 43, 56, 52, 45, 50, 48, 42, 58, 57, 56]
company_maclr_gross = [49, 44, 40, 46, 52, 46, 46, 59, 50, 60, 45, 52]
company_dain_gross = [49, 44, 41, 51, 58, 52, 42, 40, 56, 50, 51, 54]

company_tech_ebit = [14, 31, 17, 26, 22, 23, 27, 20, 17, 18, 31, 11]
company_speed_ebit = [14, 28, 13, 26, 25, 17, 21, 26, 19, 38, 33, 33]
company_maclr_ebit = [16, 22, 13, 18, 24, 19, 20, 33, 26, 38, 16, 30]
company_dain_ebit = [16, 23, 13, 26, 30, 28, 20, 17, 28, 29, 29, 29]

company_tech_net = [10, 21, 11, 18, 15, 15, 18, 13, 12, 13, 21, 7]
company_speed_net = [14, 11, 8, 18, 17, 11, 14, 17, 13, 26, 22, 22]
company_maclr_net = [12, 14, 8, 12, 16, 12, 13, 22, 18, 26, 11, 19]
company_dain_net = [10, 16, 9, 17, 21, 18, 13, 11, 19, 20, 20, 19]

data_gross = pd.DataFrame({
    'Quarter': quarters,
    'Tech': company_tech_gross,
    'Speed': company_speed_gross,
    'Maclr': company_maclr_gross,
    'Dain': company_dain_gross,
    'Measure': 'Gross_Profit_Margin'
})

data_ebit = pd.DataFrame({
    'Quarter': quarters,
    'Tech': company_tech_ebit,
    'Speed': company_speed_ebit,
    'Maclr': company_maclr_ebit,
    'Dain': company_dain_ebit,
    'Measure': 'EBIT_Margin'
})

data_net = pd.DataFrame({
    'Quarter': quarters,
    'Tech': company_tech_net,
    'Speed': company_speed_net,
    'Maclr': company_maclr_net,
    'Dain': company_dain_net,
    'Measure': 'Net_Profit_Margin'
})

data = pd.concat([data_gross, data_ebit, data_net], ignore_index=True)

# Use melt to reshape the data
data = pd.melt(data, id_vars=['Quarter', 'Measure'], value_vars=['Tech', 'Speed', 'Maclr', 'Dain'], 
               var_name='Company', value_name='Value')

# Create a numeric quarter column for trend lines
data['Quarter_Num'] = pd.Categorical(data['Quarter'], categories=quarters, ordered=True).codes

# Define custom color palette
my_palette = {
    "Tech": "darkslateblue", 
    "Speed": "lightseagreen", 
    "Maclr": "darkgoldenrod", 
    "Dain": "coral"
}

# Plot function
def plot_measure(data, measure, y_label):
    data_filtered = data[data['Measure'] == measure]
    
    fig = go.Figure()

    for company in data_filtered['Company'].unique():
        company_data = data_filtered[data_filtered['Company'] == company]
        fig.add_trace(go.Bar(
            x=company_data['Quarter'],
            y=company_data['Value'],
            name=company,
            marker_color=my_palette[company]
        ))
        fig.add_trace(go.Scatter(
            x=company_data['Quarter'],
            y=company_data['Value'],
            mode='lines',
            name=f'{company} Trend',
            line=dict(color=my_palette[company])
        ))

    fig.update_layout(
        title=f'{measure} Comparison',
        xaxis_title='Year-Quarter',
        yaxis_title=y_label,
        barmode='group'
    )

    return fig

# Plot Gross Profit Margin
Gross_Margin = plot_measure(data, 'Gross_Profit_Margin', 'Gross Profit Margin (%)')
Gross_Margin.show()

# Plot EBIT Margin
EBIT_Margin = plot_measure(data, 'EBIT_Margin', 'EBIT Margin (%)')
EBIT_Margin.show()

# Plot Net Profit Margin
Net_Profit_Margin = plot_measure(data, 'Net_Profit_Margin', 'Net Profit Margin (%)')
Net_Profit_Margin.show()
Conclusions_4:¶

The graphs indicate that all companies exhibit an upward trend in their Margin Ratios, with increases observed each quarter. However, Tech stands out with a different, downward trend. Additionally, it is important to note that Speed Company is the dominant player in this context.

In [74]:
import pandas as pd
import plotly.express as px
import plotly.graph_objects as go

# Define the data
quarters = ['21-Q1', '21-Q2', '21-Q3', '21-Q4', 
            '22-Q1', '22-Q2', '22-Q3', '22-Q4', 
            '23-Q1', '23-Q2', '23-Q3', '23-Q4']

company_tech_roa = [9, 15, 10, 33, 19, 11, 13, 27, 10, 8, 20, 16]
company_speed_roa = [10, 13, 5, 29, 15, 7, 16, 26, 10, 21, 26, 45]
company_maclr_roa = [12, 16, 10, 11, 17, 19, 10, 27, 27, 16, 7, 17]
company_dain_roa = [11, 16, 9, 17, 17, 14, 12, 10, 17, 20, 18, 19]

company_tech_roe = [17, 27, 14, 32, 26, 37, 22, 27, 20, 17, 25, 25]
company_speed_roe = [15, 19, 12, 24, 31, 13, 42, 23, 17, 60, 31, 47]
company_maclr_roe = [24, 44, 27, 19, 34, 54, 29, 48, 80, 24, 11, 35]
company_dain_roe = [28, 30, 20, 29, 28, 22, 21, 21, 27, 51, 33, 43]

company_tech_oroa = [28, 26, 16, 21, 24, 17, 25, 20, 13, 15, 29, 9]
company_speed_oroa = [20, 27, 9, 26, 19, 13, 24, 20, 14, 35, 35, 23]
company_maclr_oroa = [32, 23, 17, 15, 23, 28, 18, 39, 34, 30, 9, 26]
company_dain_oroa = [36, 24, 16, 27, 19, 23, 18, 17, 24, 29, 25, 30]

data_roa = pd.DataFrame({
    'Quarter': quarters,
    'Tech': company_tech_roa,
    'Speed': company_speed_roa,
    'Maclr': company_maclr_roa,
    'Dain': company_dain_roa,
    'Measure': 'ROA'
})

data_roe = pd.DataFrame({
    'Quarter': quarters,
    'Tech': company_tech_roe,
    'Speed': company_speed_roe,
    'Maclr': company_maclr_roe,
    'Dain': company_dain_roe,
    'Measure': 'ROE'
})

data_oroa = pd.DataFrame({
    'Quarter': quarters,
    'Tech': company_tech_oroa,
    'Speed': company_speed_oroa,
    'Maclr': company_maclr_oroa,
    'Dain': company_dain_oroa,
    'Measure': 'Operating_ROA'
})

data = pd.concat([data_roa, data_roe, data_oroa], ignore_index=True)

# Use melt to reshape the data
data = pd.melt(data, id_vars=['Quarter', 'Measure'], value_vars=['Tech', 'Speed', 'Maclr', 'Dain'], 
               var_name='Company', value_name='Value')

# Create a numeric quarter column for trend lines
data['Quarter_Num'] = pd.Categorical(data['Quarter'], categories=quarters, ordered=True).codes

# Define custom color palette
palette_colors = {
    "Tech": "darkslateblue", 
    "Speed": "lightseagreen", 
    "Maclr": "darkgoldenrod", 
    "Dain": "coral"
}

# Plot function
def plot_measure(data, measure, y_label):
    data_filtered = data[data['Measure'] == measure]
    
    fig = go.Figure()

    for company in data_filtered['Company'].unique():
        company_data = data_filtered[data_filtered['Company'] == company]
        fig.add_trace(go.Bar(
            x=company_data['Quarter'],
            y=company_data['Value'],
            name=company,
            marker_color=palette_colors[company]
        ))
        fig.add_trace(go.Scatter(
            x=company_data['Quarter'],
            y=company_data['Value'],
            mode='lines',
            name=f'{company} Trend',
            line=dict(color=palette_colors[company])
        ))

    fig.update_layout(
        title=f'{measure} Comparison',
        xaxis_title='Year-Quarter',
        yaxis_title=y_label,
        barmode='group'
    )

    return fig

# Plot ROA
ROA = plot_measure(data, 'ROA', 'ROA (%)')
ROA.show()

# Plot ROE
ROE = plot_measure(data, 'ROE', 'ROE (%)')
ROE.show()

# Plot Operating ROA
OROA = plot_measure(data, 'Operating_ROA', 'Operating ROA (%)')
OROA.show()
Conclusions_5:¶

Speed: In terms of profitability, the key indicators, ROE (Return on Equity) and OROA (Operating Return on Operating Assets), show positive performance for two reasons. Firstly, OROA remains strong because the company has maintained stable inventory turnover (meaning the period in which the inventory is quickly sold). Secondly, ROE has remained stable due to the improvement and consistency in the Dupont Analysis.

Maclr & Dain: They are relatively stable in their Operating Return on Assets (OROA) measure. However, Dain's Return on Equity (ROE) shows an upward trend due to its high efficiency in increasing its ROE. This efficiency suggests that Dain's ROE could match that of Maclr in the coming years.

Tech: It appears to be the worst-case scenario compared to other companies in its sector, particularly due to its downward trend in returns from operating activities.

Additional Support Analysis Graphs_6¶

In [75]:
import pandas as pd
import plotly.express as px
import plotly.graph_objects as go
import plotly.io as pio

# Set the default renderer to 'notebook' or 'notebook_connected'
pio.renderers.default = 'notebook'

# Define the data
quarters = ['21-Q1', '21-Q2', '21-Q3', '21-Q4', 
            '22-Q1', '22-Q2', '22-Q3', '22-Q4', 
            '23-Q1', '23-Q2', '23-Q3', '23-Q4']

tech_values_cogs = [0.56, 0.46, 0.60, 0.52, 0.49, 0.54, 0.53, 0.53, 0.55, 0.56, 0.45, 0.60]
speed_values_cogs = [0.51, 0.59, 0.57, 0.44, 0.48, 0.55, 0.50, 0.52, 0.58, 0.42, 0.43, 0.44]
maclr_values_cogs = [0.54, 0.56, 0.60, 0.54, 0.48, 0.54, 0.54, 0.41, 0.50, 0.40, 0.55, 0.48]
dain_values_cogs = [0.54, 0.56, 0.59, 0.49, 0.42, 0.48, 0.58, 0.60, 0.44, 0.50, 0.49, 0.46]

data_cogs = pd.DataFrame({
    'Quarter': quarters * 4,
    'Company': ['Tech']*len(quarters) + ['Speed']*len(quarters) + ['Maclr']*len(quarters) + ['Dain']*len(quarters),
    'COGS_to_Revenue_Ratio': tech_values_cogs + speed_values_cogs + maclr_values_cogs + dain_values_cogs
})

# Define custom color palette
my_palette = {
    "Tech": "darkslateblue", 
    "Speed": "lightseagreen", 
    "Maclr": "darkgoldenrod", 
    "Dain": "coral"
}

# Plot COGS to Revenue Ratio with linear trends
fig_cogs = go.Figure()

for company in data_cogs['Company'].unique():
    company_data = data_cogs[data_cogs['Company'] == company]
    fig_cogs.add_trace(go.Bar(
        x=company_data['Quarter'],
        y=company_data['COGS_to_Revenue_Ratio'],
        name=company,
        marker_color=my_palette[company]
    ))
    fig_cogs.add_trace(go.Scatter(
        x=company_data['Quarter'],
        y=company_data['COGS_to_Revenue_Ratio'],
        mode='lines',
        name=f'{company} Trend',
        line=dict(dash='dash', color=my_palette[company])
    ))

fig_cogs.update_layout(
    title='COGS to Revenue Ratio Comparison',
    xaxis_title='Year-Quarter',
    yaxis_title='COGS to Revenue Ratio',
    barmode='group'
)

fig_cogs.show()

# Define the data for Operating Expenses
tech_values_exp = [189537, 127755, 172230, 143990, 256685, 121972, 133764, 250773, 178966, 150406, 212269, 212877]
speed_values_exp = [186260, 127755, 163765, 234290, 171591, 156534, 286637, 142805, 132368, 139115, 216327, 117837]
maclr_values_exp = [155277, 127755, 231040, 174450, 173750, 226940, 153783, 218594, 194734, 137244, 148695, 157471]
dain_values_exp = [174002, 127755, 272291, 243956, 145761, 136949, 147043, 209652, 219762, 195881, 166597, 225678]

data_op_expenses = pd.DataFrame({
    'Quarter': quarters * 4,
    'Company': ['Tech']*len(quarters) + ['Speed']*len(quarters) + ['Maclr']*len(quarters) + ['Dain']*len(quarters),
    'Operating_Expenses': tech_values_exp + speed_values_exp + maclr_values_exp + dain_values_exp
})

# Plot Operating Expenses with linear trends
fig_expenses = go.Figure()

for company in data_op_expenses['Company'].unique():
    company_data = data_op_expenses[data_op_expenses['Company'] == company]
    fig_expenses.add_trace(go.Bar(
        x=company_data['Quarter'],
        y=company_data['Operating_Expenses'],
        name=company,
        marker_color=my_palette[company]
    ))
    fig_expenses.add_trace(go.Scatter(
        x=company_data['Quarter'],
        y=company_data['Operating_Expenses'],
        mode='lines',
        name=f'{company} Trend',
        line=dict(dash='dash', color=my_palette[company])
    ))

fig_expenses.update_layout(
    title='Operating Expenses Comparison',
    xaxis_title='Year-Quarter',
    yaxis_title='Operating Expenses',
    yaxis=dict(tickformat=','),
    barmode='group'
)

fig_expenses.show()
Conclusions_6:¶

All companies are interconnected in terms of costs, as they operate within the same commercial sector and benefit from stable economic conditions.

Tech: Now, addressing the crucial point, the company has declining Margins despite the surrounding companies not experiencing similar issues. If we look at the Gross Profit Margin, it is lower compared to other companies. This could be due to one of two reasons: either the company is not well-known and thus sells less, which is unlikely since the data shows that all four companies have nearly equal profits, or the second and more likely reason is that Tech's product pricing is higher than that of other companies. Upon investigating, it was found that Tech has higher Costs of Goods Sold compared to the other companies.

Regarding the EBIT Margin (Earnings Before Interest and Tax), it has been low over the years, also due to higher Operating Expenses than the other companies. Ultimately, this has resulted in a lower Net Profit Margin. Additionally, the lower debt levels compared to other companies have led to higher taxes for Tech compared to its peers.

Section 5: The Solutions For Tech:¶

1-Cost Analysis and Value Engineering: The first step Tech should take is to thoroughly examine its costs by implementing value engineering techniques. This involves identifying and eliminating all non-value-added activities to reduce overall costs. By doing so, the company can lower its product prices, thereby increasing its Gross Profit Margin and enhancing its inventory turnover.

2-Implementing Appropriate Controls on Accounts Receivables: After reducing prices, the company should introduce suitable controls on Accounts Receivables that align with its market position. This strategy will help increase the Receivable Turnover, thereby shortening the Cash Conversion Cycle. Consequently, this will improve liquidity and boost operating activities.

3-Optimizing Debt-to-Equity Ratio: Once the company stabilizes and its market value increases, it can leverage the third crucial factor: optimizing its Debt-to-Equity ratio. By aligning its debt levels with its financial position and liquidity at that time, the company can increase its Net Profit Margin and benefit from a reduction in taxes.

Implementing these strategies will enhance Tech’s competitiveness with Speed and other Companies and improve its overall financial performance.